Display Previous Days sales excluding Weekends In PowerBI

1
votes

I'm having trouble creating a measure that will display the total sales of the previous day minus weekends. The goal is if its a Monday that visual would show sales data for the previous Friday. If it's a Saturday or Sunday it still displays Friday.

I have already created a rolling calendar in PowerBI and adding a calculated column to that lists a 1 and 0 for it the date is a weekend or weekday.

The measure I have written display the previous day but I can't figure out how to filter so the previous day shown never is a weekend:

PreviousDaysSamples = CALCULATE([Total Samples], PREVIOUSDAY(Dates[Date.1]))

Any help would be great. I've looked and just haven't been able to find any solution yet.

enter image description here

1
powerbi

1 Answers

0
votes

You will need to add in an IF condition.

For example, the below will check if the day of the week is a Monday (DAYOFWEEK = 1) and then use Fridays data DATEADD(Dates[Date.1], -2, day), else use the previous day for all other days. You would need to add in a few more conditions to cater for the Saturday and Sundays to get back to the Friday, but this example should put you on the right track. 

PreviousDaysSamples = IF (DAYOFWEEK(Dates[Date.1] = 1), 
CALCULATE([Total Samples], DATEADD(Dates[Date.1], -2, day)),  
CALCULATE([Total Samples], PREVIOUSDAY(Dates[Date.1])))

There are some additional techniques listed here