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
    )
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Sure thing

 

 

A couple of dates as an example. We need to add these days to the date, but make sure that only the work days gets added, skip saturday and sunday

Anonymous's avatar
Anonymous
Not applicable
8 years ago

v-chuncz-msft

As an example to further explane what i mean

 

the date 02.04.2017 with 16 days transport time would need the result to be 24.04.2017 when just counting work days.

and NOT  18.04.2017 (countet with weekends)

  • sdjensen's avatar
    sdjensen
    Solution Sage
    8 years ago

    Try adding a working days index to your period table. If a day is not a working day then assign the same index number to this day as the previous day - then you should be able to calculate a column in your table where you find the index of your initial date add the transportation days to the index and return the lowest date with that index.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      sdjensen

      thank you for the reply

       

      Could you give me an example on how you would write that? i am still very new to writing my own columns/measures

      • sdjensen's avatar
        sdjensen
        Solution Sage
        8 years ago

        This would be much easier if you shared a base model with some date - at least the data you posted earlier (as a picture) and a period table.

         

        But to calculate the index in your period table you could try something like this.

         

        Column1:
IsWorkingDay = IF( Period[DayOfWeekNumber] = 6 || Period[DayOfWeekNumber] = 7; 0; 1 ) 

Column2:
WorkingDaysIndex = 
RANKX(
    FILTER(
        Period;
        Period[IsWorkingDay] = 1
    );
    Period[Date];
    ;ASC
)

         

        IsWorkingDay calculates a column that will return a 1 if the day is not a Saturday or Sunday.

        WorkingDaysIndex then calculates the index based on IsWorkingDay.

         

        I can see from my small test that the rank on a Saturday is 1 larger than on a Friday, so instead of MIN date you should find the MAX date where index is equal Index from starting date + Transportation days