Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help in finding Due date

Hi Everyone,   I am new to this portal, I need help in finding dates. I have ticket received dates,  I want to add 10 days to it, but if there is weekends or holidays within this 10 days then need...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Here is the sample file with the solution for both a calculated column and a measure https://we.tl/t-jwOgrzLuxM

    For calculated column please use

    Due Date = 
VAR CurrentDate = Tickets[Ticket St date]
VAR T1 =
    CALENDAR ( CurrentDate, CurrentDate + 15 )
VAR T2 =
    FILTER ( T1, NOT ( WEEKDAY ( [Date] ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( 'Australia holidays'[Date] ) ) )
VAR T3 =
    TOPN ( 10, T2, [Date], ASC )
RETURN
    MAXX ( T3, [Date] )

    Due Date Measure = 
VAR CurrentDate = SELECTEDVALUE ( Calendar_Table[Date] )
RETURN
    IF (
        NOT ISBLANK ( CurrentDate ),
        VAR T1 =
            CALENDAR ( CurrentDate, CurrentDate + 15 )
        VAR T2 =
            FILTER ( T1, NOT ( WEEKDAY ( [Date] ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( 'Australia holidays'[Date] ) ) )
        VAR T3 =
            TOPN ( 10, T2, [Date], ASC )
        RETURN
            MAXX ( T3, [Date] )
    )

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Anonymous , because there is no set pattern for bank holidays in the UK (and I assume in most countries), the only way to be sure is to have a Date table or a Calendar table, with a column that specifies whether each particular date is a working day or not. You will need to configure and maintain that table.

 

Once you have the Date or Calendar table in place, you can create a calculated column or measure that uses the IsWorkingDay column to work out what you want.