cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

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.
Thanks a lot folks,

2 REPLIES 2
Frequent Visitor

Hello again folks,
Well, after a second look, it seems that Alberto Ferrari had the answer to my question in this video :

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,

Frequent Visitor

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.

Thanks,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors