Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding 3 Workdays to a Date

Please Help,

I have worked on this for some time and can't seem to find a solution online. 

I am needing to add 3 Workdays to an Application date. The application date can be any day of the year, but the workdays can not inlcude weekends or holidays.

 

So if the ApplicationDate is 8/18/2021, the date I need returned would be 8/23/2021.

If the ApplicationDate is 8/21/2021, the date I need returned would be 8/25/2021.

 

I am getting close, I just can't seem to get it exact 100%. 

 

 

 

 

 

Working Day = 
SWITCH(
    TRUE(),
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] <> 0
    && 'Calendar'[Day of Week] <> 6 , "Working Day",
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] = 0
    || 'Calendar'[Day of Week] = 6 , "Weekend",
    "Holidays")
IsWorkDay = SWITCH(
    TRUE(),
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] <> 0
    && 'Calendar'[Day of Week] <> 6 , "2",
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] = 0
    || 'Calendar'[Day of Week] = 6 , "1",
    "1")
Rank = RANKX(FILTER('Calendar','Calendar'[IsWorkDay]=2),'Calendar'[FullDateAlternateKey],,ASC,Dense)
Add3Days = LOOKUPVALUE('Calendar'[FullDateAlternateKey],'Calendar'[IsWorkDay],2,'Calendar'[Rank],'Calendar'[Rank]+3)

 

 

 

 

Thank you,

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Since counting in PQ starts at 0, you need to subtract 1 from the working days to get correct value from the list of working days.

     

    Here is the function version, updated with that.

     

     

    (startdate as date, workingdays as number) =>
    let
    Source = List.Select(
    List.Dates( // make a list of dates
    startdate,
    workingdays + 1 + Number.RoundUp(workingdays / 5, 0) * 2, // go out far enough to include due date in working days
    #duration(1, 0, 0, 0)
    ),
    each List.Contains({1..5}, Date.DayOfWeek(_)) // keep just dates that are Monday through Friday
    ){workingdays - 1} // get the needed value from the list you've created
    in
    Source
     
    Pat

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I have watched this video and it doesnt work for what I am trying to do. I saw that you posted this on another thread. I am not sure you read my post.

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        5 years ago

        The approach should work here too.  Basically, in DAX or M, you filter out a list/table of the dates beyond your start date (go out 5-7 days based on worst-case scendario of weekend/holiday that might occur), filter those to just working days, and choose the 3rd value in your case.

         

        Pat

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    I create a sample to demonstrate the solution. Basically I created flag and rank column before getting the add 3 days column, check the pbix if needed.

     

    isworkingday = IF([Working Day]="Workingday",TRUE())
    rank = RANKX(FILTER('Table',[isworkingday]=TRUE()),[Date],,AS
    Add3days = CALCULATE(MAX([Date]),FILTER('Table',[rank]=EARLIER([rank])+3))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      What I did is bascially the same thing. You will notice on first date, you added 3 days and it brings back 1/7/21. That's incorrect. It should be 1/6/21.