Forum Discussion
DAX- Next Working Day
Hi Rui_Mateus ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
NextWorkingDay = VAR NextWorkingDay = MINX ( FILTER ( 'Table', 'Table'[Day] > EARLIER ( 'Table'[Day] ) && 'Table'[IsWorkingDay] = "Y" ), 'Table'[Day] ) RETURN IF ( 'Table'[IsWorkingDay] = "N", NextWorkingDay, 'Table'[Day] +1)(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionYou're right. The approach I provided is more suited for calculated columns rather than measures. If you want to create a measure that dynamically calculates the next working day for each date in your visualizations or aggregations, you can use a slightly different approach using the MAX or MIN functions along with the EARLIER function to reference the current row context.
Here's how you can create a measure to get the next working day based on the sales date:
NextWorkingDayMeasure =
VAR CurrentDate = MAX('FactTable'[SalesDate]) // Use MAX to get the date in the current context
VAR NextDay = CurrentDate + 1
VAR NextDayOfWeek = WEEKDAY(NextDay)
VAR Adjustment = SWITCH(
NextDayOfWeek,
6, 2, // If Saturday, move to Monday (2 days ahead)
7, 1, // If Sunday, move to Tuesday (1 day ahead)
1, 1 // If Monday to Friday, stay the same
)
RETURN
IF(
NextDayOfWeek = 6 || NextDayOfWeek = 7,
NextDay + Adjustment,
NextDay
)
When you use this measure in a visualization or a table, the MAX('FactTable'[SalesDate]) will get the sales date for the current row context. The rest of the DAX logic will then calculate the next working day based on this date.
Remember that using measures in this way could potentially lead to performance issues, especially if you're dealing with a large dataset. If you find performance issues or if the measure doesn't work as expected, you might want to consider creating a calculated column as initially suggested, but ensure that you evaluate the trade-offs between performance and functionality.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hello again,
Thanks for the answer.
It´s works almost 100 %, but I have another problem.
In same cases, days of the week are holiday and the measure doesn´t work.
How can we solve this question ?
Thank you for our help
- 123abc
To handle holidays along with weekends, you can extend the approach by adding another column to your calendar table that identifies holidays. Once you have that column, you can modify the DAX formula to account for holidays as well when determining the next working day.
Here's how you can do it step-by-step:
Add a Holiday Column to Calendar Table: Create a new column in your calendar table to identify holidays. You can manually populate this column with dates that are holidays or use another method to identify holidays based on specific criteria.
IsHoliday =
IF(
// Add conditions to check if the date is a holiday
FALSE(), // Change this to TRUE() for specific dates you want to mark as holidays
TRUE(),
FALSE()
)Modify the DAX Formula for Next Working Day: Now that you have a column in the calendar table that identifies holidays, you can modify the NextWorkingDay formula in your fact table to also consider holidays when determining the next working day.
Here's the revised DAX formula:
NextWorkingDay =
VAR NextDay = 'FactTable'[SalesDate] + 1VAR IsCurrentDateWeekend =
IF(
LOOKUPVALUE(Calendar[IsWeekend], Calendar[Date], 'FactTable'[SalesDate]) = TRUE(),
TRUE(),
FALSE()
)VAR IsNextDayWeekend =
IF(
LOOKUPVALUE(Calendar[IsWeekend], Calendar[Date], NextDay) = TRUE(),
TRUE(),
FALSE()
)VAR IsNextDayHoliday =
IF(
LOOKUPVALUE(Calendar[IsHoliday], Calendar[Date], NextDay) = TRUE(),
TRUE(),
FALSE()
)RETURN
IF(
IsCurrentDateWeekend = TRUE() || IsNextDayHoliday = TRUE(),
IF(
IsNextDayWeekend = TRUE() || IsNextDayHoliday = TRUE(),
'FactTable'[SalesDate] + SWITCH(WEEKDAY('FactTable'[SalesDate]), 6, 2, 7, 1),
NextDay
),
'FactTable'[SalesDate]
)In this revised formula, I added a IsNextDayHoliday variable that checks if the next day is a holiday. If either the current date is a weekend or the next day is a holiday or a weekend, the formula adjusts the date to the following working day (Monday if it's Saturday and Tuesday if it's Sunday). This way, you can handle both weekends and holidays when determining the next working day for sales dates.
Remember to adjust the IsHoliday column logic according to how you want to identify holidays in your calendar table.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.