Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Adding only workdays to a specific date

Hello   I have a data column i have a number of days column these days can wary from 1-50 days   What i need help figuring out, is how do i subract those days in "workdays" from that date and t...
Need Help
  • sdjensen's avatar
    sdjensen
    8 years ago

    Okay - to assist others with similar challenges I will just make a short description of the solution I created.

     

    First make sure that there is a relationship between the table and the calendar table.

     

    Then in the calendar table create a few columns to create an index for workingdays

    IsWorkday = SWITCH( WEEKDAY('Calendar'[Date]); 1; 0; 7; 0;1 )

WorkingDayIndex = 
RANKX(
    FILTER(
        'Calendar';
        'Calendar'[IsWorkDay] = 1
    );
    'Calendar'[Date];
    ;ASC
)

     

    Then in the table that should hold the result I created this calculation that make a simple lookup in the calendar table using the index column created above.

    NewDate = 
VAR DateIdx = CALCULATE( MAX( 'Calendar'[WorkingDayIndex] ); 'Table1'[Date] = RELATED('Calendar'[Date] ) )
VAR NewDateIdx = DateIdx + Table1[Transport days]

RETURN
CALCULATE(
    MAX('Calendar'[Date]);
    FILTER(
        ALL('Calendar');
        'Calendar'[WorkingDayIndex] = NewDateIdx 
            && 'Calendar'[IsWorkday] = 1
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

Anonymous,

 

You may refer to the following DAX that adds a calculated column.

Column =
VAR d = Table1[Date]
RETURN
    MAXX (
        FILTER (
            'Calendar',
            VAR d2 = 'Calendar'[Date] RETURN 'Calendar'[IsWorkday] = 1
                && CALCULATE (
                    COUNTROWS ( 'Calendar' ),
                    ALL ( 'Calendar' ),
                    DATESBETWEEN ( 'Calendar'[Date], d + 1, d2 ),
                    'Calendar'[IsWorkday] = 1
                )
                    = VALUE ( Table1[Transport days] )
        ),
        'Calendar'[Date]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

    v-chuncz-msft 

    Hello again, thank you for youre reply

    I am running into this issue, any surgestions on how to fix this?

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    Anonymous,

     

    Use the method from sdjensen to obtain good performance.

     

    sdjensen,

     

    The result might need to be shown as below.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    sdjensen and sdjensen

    Thank you very mutch for youre help!

    sdjensen youre solution works perfectly now. Thank you very mutch again

    This just made my life alot easier ;)

  • sdjensen's avatar
    sdjensen
    Solution Sage
    8 years ago

    Anonymous - you are welcome, but I didn't make any changes? So how do you all of a sudden get the right dates?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I was working on a demo for you to check out, then i realised that i had set the relationship from calender to the table with the wrong date. So that made the calculation all wrong. When i linked it to the right on it worked like a charm!

    Abit embarrassing but it happens

  • sdjensen's avatar
    sdjensen
    Solution Sage
    8 years ago

    Okay - to assist others with similar challenges I will just make a short description of the solution I created.

     

    First make sure that there is a relationship between the table and the calendar table.

     

    Then in the calendar table create a few columns to create an index for workingdays

    IsWorkday = SWITCH( WEEKDAY('Calendar'[Date]); 1; 0; 7; 0;1 )

WorkingDayIndex = 
RANKX(
    FILTER(
        'Calendar';
        'Calendar'[IsWorkDay] = 1
    );
    'Calendar'[Date];
    ;ASC
)

     

    Then in the table that should hold the result I created this calculation that make a simple lookup in the calendar table using the index column created above.

    NewDate = 
VAR DateIdx = CALCULATE( MAX( 'Calendar'[WorkingDayIndex] ); 'Table1'[Date] = RELATED('Calendar'[Date] ) )
VAR NewDateIdx = DateIdx + Table1[Transport days]

RETURN
CALCULATE(
    MAX('Calendar'[Date]);
    FILTER(
        ALL('Calendar');
        'Calendar'[WorkingDayIndex] = NewDateIdx 
            && 'Calendar'[IsWorkday] = 1
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sdjensen 

     

    3 years later and it's still relevant.

     

    I am trying to do the same thing the original poster was doing, except I just want to add 3 days to a date and exclude weekends.

     

    I created the IsWorkday Column.

     

    I created the WorkingDayIndex however, I do not understand the RANKX need.

     

     When creating a measure for this, I am not able to add this Date field.

    NewDate = 
VAR DateIdx = CALCULATE( MAX( 'Calendar'[WorkingDayIndex] ); 'Table1'[Date] = RELATED('Calendar'[Date] ) )
VAR NewDateIdx = DateIdx + Table1[Transport days]

      Any help would be greatly appreciated.