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
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.