Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Display Yesterdays Data (Monday displays last work day aka Friday)

Hello,

I am currently working on a project in which i want to display work requests and work completed for yesterday in which my data will be refreshing daily.

I have worked out the following formula to display yesterdays date:

YesterdayWR = IF(wr_view[Date Work Requested] = TODAY()-1, 1, 0)

So within my visuals I simply place a filter to display when value is 1 which gives me yesterdays date.

Now my problem comes in when the day is monday i no longer want yesterdays date i want the last working date (in our case Friday)

What I have done so far is the following

Created column with numbers for the date:

DayWC = WEEKDAY(wr_view[Date Work Completed])

and

DayNameWC = FORMAT( wr_view[Date Work Completed], "dddd" )

Which results in the following table in which I want to be able to display yesterdays date but when the day is monday count yesterday as friday's date

weekday.png

If there is a nice elegent solution that kind of follows my logic I would be pleased to see the insight into this.

Any help is greatly appreciated.

Thanks.

1 ACCEPTED SOLUTION

Hi @Anonymous

 

In the RETURN part of your formula, shouldn't HELP be compared with values in your date column? Your formula would then be:

 

 

YesterdayWRSwitch =
VAR HELP =
    IF ( FORMAT ( TODAY (), "dddd" ) = "Monday", TODAY () - 3, TODAY () - 1 )
RETURN
    IF ( HELP = 'Table'[Date], 1, 0 )

And then just filter the card or any other visuals containing the measure to YesterdayWRSwitch = 1.

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

Have a look at my Quick Measure here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545

 

Should be able to write the logic that if it is Monday, then grab last week's LastWorkingDay.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks for your post

I have been messing around with thinking through this logic

I am not all too familair with DAX currently and am still struggling to achieve the end goal of this logic

I am curious. What if today was Sunday? Would you also consider it as Friday?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

As far as i am concerned the view won't be important for use on weekends seeing as it will be on a board in the office and people will only see it monday through friday

You could also use the technique here: 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 

to exclude Sundays and Saturdays. If you add a column to return the working day of the year or perhaps use sequential, then you can probably make the calculation easier.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

So this logically makes sense that it would display what I want but i am clearly doing something wrong here

YesterdayWRSwitch = 
VAR HELP = IF(FORMAT(TODAY(), "dddd") = "Monday", TODAY()-3, TODAY()-1)
RETURN IF(HELP, 1, 0)

all im trying to do is if monday today -3 makes it friday

else we just -1 because that gives us yesterday
I want a boolean value of 1 or 0 so i can filter the whole visual to just showing yesterday but with this it didn't work out?

Hi @Anonymous

 

In the RETURN part of your formula, shouldn't HELP be compared with values in your date column? Your formula would then be:

 

 

YesterdayWRSwitch =
VAR HELP =
    IF ( FORMAT ( TODAY (), "dddd" ) = "Monday", TODAY () - 3, TODAY () - 1 )
RETURN
    IF ( HELP = 'Table'[Date], 1, 0 )

And then just filter the card or any other visuals containing the measure to YesterdayWRSwitch = 1.

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Thanks, this seems to be working now

Take a look at this Quick Measure I just posted. You should be able to use this to simply filter to the current working day number minus 1.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Day-Number-of-Year-and-Working-Day-Number-of...

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.