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

     

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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] )
    )

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

       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's avatar
        Jihwan_Kim
        Super User
        4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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