topic Re: DAX measure to get the resulting EndDate after adding N week days to a given StartDate in DAX Commands and Tips

DAX measure to get the resulting EndDate after adding N week days to a given StartDate

Nyansapo — Wed, 19 Jul 2023 04:38:39 GMT

Hello Power BI community,
I'm writting to you for the first time because for the first time, I haven't been able to find my answer elswhere on the Internet. I found some similar threads for calculated columns (among which a video from Alberto Ferrari) and for Power Query, but nothing that I was able to use for this problem.
Here is what I try to code in DAX.

I need to create a measure that will take as input a StartDate (in the contextual row) as well as a given integer number (called N) (normally between 0 and 30) and will return the EndDate resulting after adding N week days (or working days) to the StartDate.

The value "N" comes from a slicer that contain value between 0 and 30, and can be selected by the user to decide how many working days he/she wants to add to the StartDate of each row contexte. Then, I expect this measure to return the proper EndDate for each of these row contexts.

Below is the code of what I tried, but for some reasons I'm not sure to understand, this measure to not work in every contexte. I would say, that it returns the proper value about 80% of the time, but as the selected value "N" becomes larger, the measure tend to return the wrong EndDate.

Test Found EndDate = // Find the EndDate resulting of the Cycle StartDate + ElapsedDays (ElapsedDays being only weekdays) //1- Value selected in the "ElapsedDays" slicer (ElapsedDays is an integer between 0 and 30) VAR ElapsedDays = SELECTEDVALUE(Slicer_Days[ElapsedDays]) //2- Cycle StartDate of the cycle in the row context VAR Cycle_StartDate = SELECTEDVALUE(SurveyCycle_Dim[Cycle StartDate]) //3- Calendar EndDate derived by simply adding ElapsedDays to the StartDate VAR Calendar_EndDate = Cycle_StartDate + ElapsedDays //4- Return number of week days between "Cycle_StartDate" and "Calendar_EndDate" VAR Num_Week_days = NETWORKDAYS(Cycle_StartDate, Calendar_EndDate, 1) //5- Derive the number of Weekend days between "Cycle_StartDate" and "Calendar_EndDate" // Number of weekend days = Total number of Days - Weekdays // Total number of days = ElapsedDays + 1 (adding 1 to account for the startdate) VAR Num_Weekend_Days = (ElapsedDays+1) - Num_Week_days //6- The first temporary EndDate of the calculation VAR First_EndDate = Cycle_StartDate + ElapsedDays + Num_Weekend_Days //7- Add number of weekend days skipped while adding weekend at the preceding step // Number of weekend days = "Total Number of days" - "Number of weekdays" VAR WeekendSkipped = (First_EndDate - Calendar_EndDate) - NETWORKDAYS(Calendar_EndDate, First_EndDate, 1) //8- Second temporary EndDate VAR Second_EndDate = First_EndDate + WeekendSkipped //9- This last step is to move to the closest next weekdays if the result fall on a Saturday or a Sunday. // Check if "Second_EndDate" is a Saturday (then add 2 to it) or a Sunaday (then add 1 to it) VAR adjust = IF(WEEKDAY(Second_EndDate,2) = 6, 2, IF(WEEKDAY(Second_EndDate,2) = 7, 1, 0) ) //10- Return the date corresponding to the Cycle StartDate + nomber of working days equal to "ElapsedDays" RETURN Second_EndDate + adjust

I'm pretty sure my error is related to the fact that I don't account properly for the weekend days I skipp and add back at the step 6 and 7, but I can't specifically find the specific error I'm making.
So far, I haven't used any Calendar table for this measure, simply because I couldn't figure how it would be usefull, but I'm open to any suggestions.
Hope to read you soon!
Thanks a lot folks,

Re: DAX measure to get the resulting EndDate after adding N week days to a given StartDate

Nyansapo — Wed, 19 Jul 2023 05:48:39 GMT

Hello again folks,
Well, after a second look, it seems that Alberto Ferrari had the answer to my question in this video :
https://www.youtube.com/watch?v=2HkBbqxBzF0

Here is his solution adapted to my contexte :

Test Found EndDate 2 = // Take the Cycle StartDate, add N working days and return the resulting EndDate // Get the number of selected ElapsedDays VAR ElapsedDays = SELECTEDVALUE(Slicer_Days[ElapsedDays]) // Define the Cycle StartDate as StartDate VAR StartDate = SELECTEDVALUE(SurveyCycle_Dim[Cycle StartDate]) // Get the list of all working days after the StartDate (this var is a subtable of Calendar table) VAR NextWorkingDates = FILTER( ALL(Calendar_Dim), Calendar_Dim[Date] > StartDate && Calendar_Dim[Is WeekDay] = 1 ) // Get the next N working days VAR Next_N_WorkingDays = TOPN(ElapsedDays, NextWorkingDates, Calendar_Dim[Date], ASC) // Get the last of the topN days of the list above VAR Last_WorkingDays = MAXX(Next_N_WorkingDays, Calendar_Dim[Date]) // I'm substracting 1 day to the final result because it's require in my contexte. RETURN Last_WorkingDays - 1

Thanks Alberto,
I'll still let this thread there as it may still help other people.
Cheers,

Re: DAX measure to get the resulting EndDate after adding N week days to a given StartDate

Nyansapo — Wed, 19 Jul 2023 15:57:22 GMT

Ok, I was so happy and I thought that this solution was perfect, but it looks like for some row contexte, it's not returning the right date...

I'm starting losing faith...

For example, when the measure gets the Start Date 2022-10-04, and the value N = 19, then it returns the date 2022-10-30, while it should return the date 2022-10-28. Indeed, when including the first and the last date, there are 19 working days between 2022-10-04 and 2022-10-28.
I have the same problem with StartDate = 2021-05-04 and N=19. The measure returns 2021-05-30, while it should return 2021-05-28.
Strangely, for these two situations, the month/year change but the days are the same...

I double checked my calendar table, and it only have weekend days for Saturday and Sunday, I haven't included any holidays.

I would be very pleased to know if anyone else get the same problem implementing the same code.

Hope to read you.
Thanks,