Power BI calculate Measure for previous working day

0
votes

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

2
powerbissasdaxbusiness-intelligence

2 Answers

0
votes

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.

0
votes

I haven't tested it, but this should work:

Yesterday Sales =
VAR Mon = CALCULATE([Sales Amount], DATEADD(Calendar[Date], -3, DAY)
VAR TueToFri = CALCULATE([Sales Amount], PREVIOUSDAY(Calendar[Date])
VAR SatSun = BLANK()

RETURN
IF(
  WEEKDAY(SELECTEDVALUE(Calendar[Date]), 2) = 1;
  Mon;
  IF(
    WEEKDAY(SELECTEDVALUE(Calendar[Date]), 2) >= 6;
    SatSun;
    TueToFri
  )
)