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
Icon for Community Support rankCommunity 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
    Icon for Solution Sage rankSolution 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
    Icon for Solution Sage rankSolution 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.