Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am looking to return the next working day as tomorrow in a Power BI column. I have the formula that works for Monday to Thursday, however, on a Friday it returns Saturday. Instead i would like Monday to be labelled as 'next working day'.
Below is the formula:
Return day = IF(DateKey[Date]=TODAY()-1,"yesterday",IF(DateKey[Date]=TODAY()+1,"next work day",IF(DateKey[Date]=TODAY(),"today",("notapplicable"))))
I am using this to filter my figures on today, next work day and also last work day.
Thanks in advance.
Solved! Go to Solution.
Believe someone else must have a smart way to go.
Basically, the following nest IF means to identify if Today is Friday or Saturday, if not, go use the common +1 logic to mark Next Working day; if it is Firday, +3 as the Next Working day, if it is Saturday, + 2 as the Next Workding day.
ReturnDay = IF ( FORMAT ( TODAY (), "DDDD" ) <> "Friday" && FORMAT ( TODAY (), "DDDD" ) <> "Saturday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 1, "Next Work Day", "Notapplicable" ) ) ), IF ( FORMAT ( TODAY (), "DDDD" ) = "Friday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 3, "Next Work Day", "Notapplicable" ) ) ), IF ( FORMAT ( TODAY (), "DDDD" ) = "Saturday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 2, "Next Work Day", "Notapplicable" ) ) ) ) ) )
Hi there
Here is the way I would do it with Variables and the SWITCH Statement
T/Y/Tom = VAR TodaysDate = TODAY () VAR YesterdayDate = TODAY () - 1 VAR NextMondaysDate = SWITCH(TRUE(), 'Date'[Date] - WEEKDAY('DateKey'[Date],2)+1 >= TodaysDate, 'DateKey'[Date] - WEEKDAY('DateKey'[Date],2)+1, TODAY()-9 ) VAR NextWorkingDay = SWITCH ( TRUE (), 'DateKey'[Date] = TodaysDate, "Today", 'DateKey'[Date] = YesterdayDate, "Yesterday", 'DateKey'[Date] = NextMondaysDate, "Next Working Day", "Not Applicable" ) RETURN NextWorkingDay
Hi @Shem,
Can you mark the proper answer as a solution please?
Best Regards,
Dale
Believe someone else must have a smart way to go.
Basically, the following nest IF means to identify if Today is Friday or Saturday, if not, go use the common +1 logic to mark Next Working day; if it is Firday, +3 as the Next Working day, if it is Saturday, + 2 as the Next Workding day.
ReturnDay = IF ( FORMAT ( TODAY (), "DDDD" ) <> "Friday" && FORMAT ( TODAY (), "DDDD" ) <> "Saturday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 1, "Next Work Day", "Notapplicable" ) ) ), IF ( FORMAT ( TODAY (), "DDDD" ) = "Friday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 3, "Next Work Day", "Notapplicable" ) ) ), IF ( FORMAT ( TODAY (), "DDDD" ) = "Saturday", IF ( 'DateKey'[Date] = TODAY () - 1, "Yesterday", IF ( 'DateKey'[Date] = TODAY (), "Today", IF ( 'DateKey'[Date] = TODAY () + 2, "Next Work Day", "Notapplicable" ) ) ) ) ) )
Thank you sooo much for your reply. The query worked perfectly! Is there any chance you could indicate how i would alter this query so that Friday gave me the next 3 days instead of just the third day.
Sorry, a change in requirements from the powers above.
Thanks,
Shem
For goe this i think i got my required formula using 'OR'
Hi there
Here is the way I would do it with Variables and the SWITCH Statement
T/Y/Tom = VAR TodaysDate = TODAY () VAR YesterdayDate = TODAY () - 1 VAR NextMondaysDate = SWITCH(TRUE(), 'Date'[Date] - WEEKDAY('DateKey'[Date],2)+1 >= TodaysDate, 'DateKey'[Date] - WEEKDAY('DateKey'[Date],2)+1, TODAY()-9 ) VAR NextWorkingDay = SWITCH ( TRUE (), 'DateKey'[Date] = TodaysDate, "Today", 'DateKey'[Date] = YesterdayDate, "Yesterday", 'DateKey'[Date] = NextMondaysDate, "Next Working Day", "Not Applicable" ) RETURN NextWorkingDay
Great, thanks this seemed to work as well as the formula above.
Add another nested IF in next working day logic:
IF(TODAY()="Friday","Monday","next work day"
The above snippet will come after the underlined part.
Return day = IF(DateKey[Date]=TODAY()-1,"yesterday",IF(DateKey[Date]=TODAY()+1,"next work day",IF(DateKey[Date]=TODAY(),"today",("notapplicable"))))
Thanks
Raj
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |