DAX CALCULATE with conditional DATEADD

[Anonymous]

Good Day.

I currently have two working measusres. The first shows total sales, and the second shows the total sales from the previous day:

MTotalSales = SUMX(AllSales,AllSales[Price]*AllSales[Quantity])

MPreviousDaySales = CALCULATE ([MTotalSales],DATEADD(AllDates[Date],-1,DAY))

This produces the following table:

What I'm now trying to do is create a previous sales. The only trick is for Monday. For Monday,

I need to show the sales from the previous Friday, and I thought the following would do it:

MPreviousDayOfWeekSales =

CALCULATE (

[MTotalSales],

FILTER(AllDates,IF (AllDates[DayOfWeek] = 1,

DATEADD ( AllDates[Date], -3, DAY ),

DATEADD ( AllDates[Date], -1, DAY ) )

)

)

Note that day of week "1" is Monday.

However, that formula just ends up showing the same day's sales:

Is there a way to build this conditional use of CALCULATE and DATEADD. Semantically:

If the day of week is not "1", then show the sales from the previous day, otherwise, show

the sales from 3 days ago?

Also, I know I'll have duplicates on Mondays-and-Saturdays, but that's okay because I'll filter the

table to only show weekdays.

Thanks in advance for your help.

[Anonymous · Accepted solution]

I ended up resolving the problem. Instead of keeping a running tabular total, I directly targeted the KPI.

Using, variables, I was able to pull off what I needed:

MPreviousWeekDaySales =
VAR ThreeDaysRange = FILTER(AllDates,AllDates[Date] = TODAY() - 3)
VAR OneDayRange = FILTER(AllDates,AllDates[Date] = TODAY() - 1)
RETURN IF(WEEKDAY(TODAY()) = 2,SUMX(ThreeDaysRange,[MTotalSales]),SUMX(OneDayRange,[MTotalSales]))