DAX- Next Working Day

[Rui_Mateus]

Good morning,

In my project i have a calendar table and a fact table with a colun with the sales day.

Both tables are related with date camp.

When sales date  is at Saturday/Sunday, I need a new colun with the next working day.

How can I do this ?

Thank in advance for all the help,

Rui

[v-tangjie-msft · Accepted solution]

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. 

[Dangar332]

Hi, @Rui_Mateus 

try below code for measure 
just adjust your table name and column name

Measure = 
var currentdate = MAX('Table'[date])
var week_day = WEEKDAY(currentdate,1)
var final = 
IF(week_day<3,
SWITCH(TRUE(),
       week_day=1,currentdate+2,
        week_day=2,currentdate+1
    ),
currentdate)
return
final

[123abc]

To create a new column in your fact table that represents the next working day based on the sales date, you can use DAX (Data Analysis Expressions) in Power BI or other tools that support DAX. Here's a step-by-step guide:

Assuming you have a Sales table with a column named "SalesDate" and a Calendar table with a column named "Date," and there's a relationship between the two tables based on the date:

1. Create a New Column in the Fact Table: Open your fact table in Power BI or other DAX-supported tool and create a new column. You can name it something like "NextWorkingDay."

2. Write the DAX Formula: Use the following DAX formula to calculate the next working day:

DAX Measure:

NextWorkingDay =
VAR CurrentDate = 'FactTable'[SalesDate]
VAR NextDay = CurrentDate + 1
VAR DayOfWeek = WEEKDAY(NextDay, 2) // 1 = Monday, 7 = Sunday

RETURN
IF(
DayOfWeek >= 6, // If Saturday or Sunday
NextDay + (8 - DayOfWeek),
NextDay
)

1. This formula checks if the next day is a Saturday or Sunday. If it is, it adds the necessary number of days to make it the next working day (Monday).

2. Apply the Formula: Drag the new column to your table in the Power BI report or refresh the data if you're using another tool.

This formula uses the WEEKDAY function with the parameter 2, which considers Monday as the first day of the week. The IF statement checks if the next day is a Saturday or Sunday, and if true, it adds the necessary number of days to get to the next working day.

Adjust the column and table names in the formula based on your actual table and column names. If your week starts on a different day, adjust the WEEKDAY parameter accordingly.

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.

[Rui_Mateus]

Good morning,

Thank you for the quick answer.

How can I do this step :

VAR CurrentDate = 'FactTable'[SalesDate]

It´s not possible to use a column in this measure.

[123abc]

You'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.

[Rui_Mateus]

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:

1. 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()
)

1. 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] + 1

VAR 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.

[Dangar332]

Hi, @Rui_Mateus 

use it as 
VAR CurrentDate = max('FactTable'[SalesDate])