Measure based on day of week

[pete.langlois]

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

[Greg]

Hi @pete.langlois

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
  • here’s @BrianJ’s YouTube video on how to anonymizing data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• 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

[jbressan]

Hi @pete.langlois

Please check this post, can be useful for you.

How to get Weekend Days to now show in table

Another option would be to add a column to the Dates table to determine if it is a workday or not then update the measure 4 days ahead with: Measure in Dates Table 4DaysAhead = CALCULATE ( MIN ( Dates[Date] ), FILTER ( ALL ( Dates[Date], Dates[IsWorkingDay] ), Dates[Date] > TODAY () + 4 && Dates[IsWorkingDay] = TRUE ) ) Column in Dates Table: IsWorkingDay = NOT WEEKDAY( ‘Dates’[Date] ) IN { 1,7 } Updated Custom column in Workticketstepheadermerge 4DaysAhead = If([4DaysAhead] >=…

[BrianJ]

@pete.langlois,

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

DAX Row minus Previous Row

@Archer, This one ended up being pretty straightforward. We just needed to take our previous measure and add the following filter conditions in the PrevDate variable. [image] If we look at 5/23 - 5/26 below, we can see that this is working as expected. Monday picks up the Friday value, but in this case so does Tuesday, since Monday is a holiday (Memorial Day - I’m assuming US holidays, but if you plug in your holiday table column parameter into the extended date table, it will work regardle…

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:

Previous Row Value

Often in a Power BI visual one wants to get the value from the previous row to use in a calculation in the current row (e.g., to see if there’s a change between the previous value and the current value). The Previous Row Value DAX pattern can be of help when faced with this issue. As with most situations in Power BI, there are many ways to solve the same problem: here are a couple: Method 1: Add DAX Index Column 1-Add a [DAX Index] calculated column to your table DAX Index = // DAX PATTERN …

I hope this is helpful.

– Brian

[EnterpriseDNA]

It’s great to know that you are making progress with your query @pete.langlois. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[EnterpriseDNA]

Hi @pete.langlois, did the response provided by @jbressan and @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @pete.langlois, we’ve noticed that no response has been received from you since the 1st of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @pete.langlois, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!