Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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.
Proud to be a 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.
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?
Proud to be a Super User!
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.
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.
Proud to be a Super User!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |