Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
2 years ago

Working days column from Date Column

I have a data table that contains a Start Date and I want a column called Target Date which is 30 working days from the Start Date.  I also have a calendar table which I can connect to the Start Date if needed.

 

Can someone help with the DAX for the column or M Code if I need to do this in Power Query?  Can I also have the option to put Holidays in ie Christmas Day, New Years day etc.

 

Many thanks

 

tamerj1 amitchandak parry2k Anonymous 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    spandy34 try this, add new column:

     

    Target Date = 
VAR __TargetDays = 30 
VAR __StartDate = Table[Start Date]
VAR __TargetDate = __StartDate + __TargetDays
VAR __TargetDateWeekDay = WEEKDAY ( __TargetDate, 3 )
VAR __AddDays = IF ( __TargetDateWeekDay  IN { 5, 6 }, 7 - __TargetDateWeekDay, 0 ) 
VAR __TargetWorkingDate = __TargetDate + __AddDays
RETURN __TargetWorkingDate
    • spandy34's avatar
      spandy34
      Responsive Resident
      2 years ago

      Thank you for responding. So I have put the following in the column but it is calculating 30 days and including weekends.  Is the Date in line 3 supposed to be from my date table or as outlined below, from the Caseload Oversight'[ASMStartDate] field?