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

[Anonymous]

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



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.

[Greg_Deckler]

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.

[Anonymous]

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

[danextian]

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

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

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?

[danextian · 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.

[Anonymous]

Thanks, this seems to be working now

[Greg_Deckler]

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...