Need Help in finding Due date

[Anonymous]

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 to extend the due date by that many days 

Ticket reveived: 4thJuly 2022

business days: 10 days

Due date: 13 July 2022 but if there is a weekend on 9th and 10th July. 

Result i want : 15th July 2022 instead of 13th July 2022

I tried Workday function in Excel. I dont know the logic to apply in DAX.

[tamerj1 · Accepted solution]

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] )
    )

[tamerj1]

Hi @Anonymous 

a simple way to doing that (calculated column) which can be converted to a measure by replacing the first variable with MAX, MIN or SELECTEDVALUE based on your requirement

Due Date =
VAR CurrentDate = TableName[Ticket reveived]
VAR T1 =
    CALENDAR ( CurrentDate, CurrentDate + 15 )
VAR T2 =
    FILTER ( T1, NOT ( CALCULATE ( WEEKDAY ( [Date] ) ) IN { 6, 7 } ) )
VAR T3 =
    TOPN ( 10, T2, [Date], ASC )
RETURN
    MAXX ( T3, [Date] )

[Anonymous]

@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.

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

It is for creating a new column without having a physical dim-calendar table. If you have one, please use your dim-calendar table.

10 working days later CC =
VAR _calendartable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
            "@weekdayname", FORMAT ( [Date], "DDDD" )
        ),
        [@weekdayname] <> "Saturday"
            && [@weekdayname] <> "Sunday"
    )
VAR _addticketreceiveddate =
    CROSSJOIN ( _calendartable, Data )
VAR _adddayscount =
    FILTER (
        ADDCOLUMNS (
            _addticketreceiveddate,
            "@dayscount",
                COUNTROWS (
                    FILTER (
                        _calendartable,
                        [Date] >= Data[TicketReceived]
                            && [Date] <= EARLIER ( [Date] )
                    )
                )
        ),
        [@dayscount] = 10
    )
RETURN
    MAXX (
        FILTER (
            _adddayscount,
            Data[TicketReceived] = EARLIER ( Data[TicketReceived] )
        ),
        [Date]
    )

[Anonymous]

 Hello Jihwan,

I was trying in my data but still getting an incorrect result.

I am attaching my file for your reference. This is my Date table

This  is my Ticket table

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please share your sample pbix file's link here, and then I can try to come up with more accurate solution for your data model.

Thanks.

[Anonymous]

Hi

I tried to upload my pbix file but i am not getting option to load the file. May be i am not allowed

[Anonymous]

Hi,

I am sharing the drop box link, wherein I have upload my .pbix file. In this file i also have my holidays list. So Now I want to exclude weekends and holidays also to calculate my due date.

https://www.dropbox.com/sh/9czd3q9slt7ddlc/AADqIzIdCUHd4UkQbh6o3viJa?dl=0 

[Jihwan_Kim]

Hi,

thank you for your sample file.

please check the attached file down below.

I fixed the formula in the ticket table.

Thank you.