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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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

        Hi

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