Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm migrating a Qliview dashboard to MS Power BI but there are some functions that Power BI does not have. One that is being used in the QV dashboard is LastWorkDate(start_date, no_of_days [, holidays]). That function returns the last working day after adding no_of_days to the start_date. Example:
LastWorkDate('19/10/2017', 2) // Will return '23/10/2017' because the 20th and 21st of october are not working days.
Also, if you specify holidays period, those days woyld be taken as not working days.
I need to convert expressions like:
IF(LastWorkDate(SHIPPING_DATE, 4)) < Floor($(varToday), "OPEN", "CLOSED")
But after searching in the forums and the Internet I cannot figure out how to put that conditional in a DAX expression for a Calculated Column.
If only we could create our custom functions...
THANKS IN ADVANCE!
Solved! Go to Solution.
Hi,
Instead of adding 4 days I've tried to add only 1 to the formula. But my surprise is that if use it for the day '23/10/2017', it returns '25/10/2017' instead of '24/10/2017'.
I've found the solution:
aux_LastWorkDate(SHIPPING_DATE) = CALCULATE( MIN(MyCalendar[Date]); FILTER( ALL(MyCalendar); MyCalendar[Date] >= (SHIPPING_DATE.[Date] + 1) && MyCalendar[isWorkDay] = 1 ) )
I don't know why this works as the date should is the same.
Hi @elxaxe,
First, you'll need to create an individual and continuous Date table if you don't have it yet.
Date = CALENDAR("2017/10/1","2017/12/31")
And add a column to the Date table that is 1 for workdays and 0 for non-workdays:
IsWorkday = SWITCH(WEEKDAY([Date]),1,0,7,0,1)
Then the DAX formula to convert LastWorkDate(SHIPPING_DATE, 4) in a calculate column should be like below.
=
VAR CURRENT_SHIPPING_DATE = SHIPPING_DATE RETURN CALCULATE ( MIN ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[Date] >= CURRENT_SHIPPING_DATE + 4 && 'Date'[IsWorkday] = 1 ) )
Regards
Thanks for the answer, @v-ljerr-msft !
Just a question from a noob like me: why are you using a variable? Wouldn't be the same if I write:
= CALCULATE ( MIN ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[Date] >= SHIPPING_DATE + 4 && 'Date'[IsWorkday] = 1 ) )
Hi @elxaxe,
Thanks for point that out! The formulas are the same, and it's simpler to use your formula in this scenario. It seems to be that I have been used to use the VAR functions to write DAX formulas.
Regards
Hi,
Instead of adding 4 days I've tried to add only 1 to the formula. But my surprise is that if use it for the day '23/10/2017', it returns '25/10/2017' instead of '24/10/2017'.
I've found the solution:
aux_LastWorkDate(SHIPPING_DATE) = CALCULATE( MIN(MyCalendar[Date]); FILTER( ALL(MyCalendar); MyCalendar[Date] >= (SHIPPING_DATE.[Date] + 1) && MyCalendar[isWorkDay] = 1 ) )
I don't know why this works as the date should is the same.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |