Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Set Due Date based on working days

Hi everyone,    I need to calculate a Due Date column based on a given date and working days.    I am working with tickets issue in a given date-time that have to be completed before a due date b...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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