Forum Discussion

Rui_Mateus's avatar
Rui_Mateus
Frequent Visitor
2 years ago
Solved

DAX- Next Working Day

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

 

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

8 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    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's avatar
      Rui_Mateus
      Frequent Visitor
      2 years ago

      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.

       

       

       

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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

      

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.