powerbissasdaxbusiness-intelligence

Power BI calculate Measure for previous working day


in Power BI I need to write a measure, which returns yesterdays Sales Amount. I can use PREVIOUSDAY() for Tuesday to Friday. However, for Monday I would like to return Friday's Sales Amount. I know I can do this with DATEADD(Calendar[Dates], -3, DAX).

My problem is that I cannot seem to get the two conditions working together. Is it even possible to do something like this with a measure?:

Yesterdays Sales = 
   Var TueToFri = CALCULATE([Sales Amount], PREVIOUSDAY(Calendar[Date])
   Var Mon = CALCULATE([Sales Amount], DATEADD(Calendar[Date], -3, DAY)

   IF WEEKDAY(Calendar[Date]) = 1
      Return Mon,
   Return TueToFri

If not, do you have any other ideas?

Cheers


Solution

  • This solution works for me:

        Sales Amount PD = 
    IF (
        WEEKDAY ( SELECTEDVALUE ( Calendar[Date] ) ) = 2;
        //Monday
        CALCULATE (
            [Sales Amount];
            DATEADD ( Calendar[Date]; -3; DAY )
        );
        IF (
            WEEKDAY ( SELECTEDVALUE ( Calendar[Date] ) ) = 1;
            //Sunday;
            BLANK ();
            IF (
                WEEKDAY ( SELECTEDVALUE ( Calendar[Date] ) ) = 7;
                //Saturday;
                BLANK ();
                CALCULATE (
                    [Sales Amount];
                    PREVIOUSDAY ( Calendar[Date] )
                )
            )
        )
    )
    

    I believe the problem was caused by the calendar table returning multiple values. By using SELECTEDVALUE() only one date is returned, which can be used to do the comparision required.