# Measure based on day of week

I have a measure that takes the sum of units sold yesterday. I have another measure that takes the sum of units sold for the day before yesterday (previous day). I’m using a date offset to achieve the logic for these measures, as follows:

Sold Qty Yesterday =

var soldqtytoday = CALCULATE([Sold Qty], FILTER(‘rpt Calendar_Master’, ‘rpt Calendar_Master’[dayoffset] = -1) )

return

IF(soldqtytoday = BLANK(), 0, soldqtytoday

Sold Qty Previous Day =

var soldpreviousday = CALCULATE([Sold Qty], FILTER(‘rpt Calendar_Master’, ‘rpt Calendar_Master’[dayoffset] = -2) )

return

IF(soldpreviousday = BLANK(), 0, soldpreviousday)

This works great so far. Except that when I look at the report on Mondays, I want to see the units sold for yesterday to sum up those for Saturday and Sunday, rather than just Sunday. I also want the previous day to equal friday instead of Saturday.

For every other day, I wat the measyre to provide values true to yesterday and previous day except for on Monday, when Yesterday = sum of Saturday and Sunday and Previous day = Friday.

For dayoffsets in my date table, I use the following logic:
today = 0,
yesterday = -1
previous day = -2
day before previous day = -3.

If I open the report on Monday, then
Monday = 0,
Sunday = -1
Saturday = -2
Friday = -3

I think there’s a way to do this using nested if statements. For Sold Qty Yesterday, I believe I need to define that if (today) = Monday, then take a sum of units sold for when dayoffset = -1 and -2 (which on monday are saturday and sunday using date offsets). Otherwise, if today = any other day, take the sum of units sold for dayoffset = -1.

For the sold qty previous day, I believe I need to use a similar logic. if(today) = Monday, then take the sum of dayoffset = -3 (which in this case would be friday).

Can someone help with the DAX so I can adjust the measures above with the right logic to account for this new logic that applies to Mondays only?

Thanks!

@BrianJ

I’d imagine this can be solved by leveraging the [Dates] table and [Is Business Day] of [Is Weekday] fields, but is dependent upon your implementation. To help the forum members further analyze your current state and visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg

Please check this post, can be useful for you.

Here’s another one I think you’ll find very relevant and useful:

Also, @Greg put together a fantastic DAX Patterns section of the forum on previous value that you should definitely take a look at if you haven’t yet: