Forum Discussion
Set Due Date based on working days
Hi Anonymous,
I'd like to suggest you use countrows with calendar functions to create variable calendar table to calculate working days.
Calculate column sample:
working days except weekend =
VAR endDate =
IF ( Table[End] <> BLANK (), Table[End], TODAY () )
VAR _calendar =
FILTER ( CALENDAR ( Table[Start], endDate ), WEEKDAY ( [Date], 2 ) <= 5 ) //calendar wihtout weekend
VAR _holidayList =
VALUES ( Holiday[Date] ) //holiday date list
RETURN
IF (
COUNTROWS ( _holidayList ) > 0,
COUNTROWS ( EXCEPT ( _calendar, _holidayList ) ),
COUNTROWS ( _calendar )
)
Regards,
Xiaoxin Sheng
Hi Anonymous,
Thank you for your answer.
That's not exactly what I am looking for. You are calculating the number of working days based in an end date. I need to do the opposite, calculate the end date based on the number of working days.
My scenario is the following: I get jobs issued with different OLAs (1, 3, 5 or 7 working days), so I need to calculate the date when the jobs are due to organise the workforce to meet as many OLAs as possible. So I need to create a calculation that given the date the job was issued and the number of working days of its OLA, I could get the date the job is due:
Due Date = fx ( Date issued, Number of working Days)
Best regards,
Vaillo
Hi Anonymous,
So you mean you want to calculate max deadline based on current date and working days?
If this is a case, you can try to use below formula:Max available deadline = VAR _workingDays = xxxx VAR _nonWorkingDays = COUNTROWS ( FILTER ( CALENDAR ( Table[Start], Table[Start] + _workingDays ), WEEKDAY ( [Date], 2 ) > 5 ) ) RETURN Table[Start] + _workingDays + _nonWorkingDaysRegards,
Xiaoxin Sheng
hi
actually i was looking the same thing . i wanted to caculate the due date based in working days.
