Forum Discussion

Dimi_2207's avatar
Dimi_2207
Helper I
2 years ago
Solved

Serial number based on condition in DAX

Hello everyone!   Could, you, please, help me with the following task in DAX. I am attaching sample in excel. I have the following columns:   1) "Date" - Call date 2) Customer ID (can be custo...
  • Jihwan_Kim's avatar
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    Repeated call in 7 days? CC = 
    VAR _currentdate = Data[Date]
    VAR _prevdate =
        SUMMARIZE (
            OFFSET (
                -1,
                Data,
                ORDERBY ( Data[Date], ASC ),
                ,
                PARTITIONBY ( Data[Customer] ),
                MATCHBY ( Data[Date], Data[Customer] )
            ),
            Data[Date]
        )
    RETURN
        SWITCH (
            TRUE (),
            _prevdate = BLANK (), "N",
            _currentdate - _prevdate > 7, "N",
            _currentdate - _prevdate <= 7, "Y"
        )

     

    EARLIER function (DAX) - DAX | Microsoft Learn

     

    Call number CC = 
    VAR _startNdate =
        MAXX (
            FILTER (
                Data,
                Data[Customer] = EARLIER ( Data[Customer] )
                    && Data[Repeated call in 7 days? CC] = "N"
                    && Data[Date] <= EARLIER ( Data[Date] )
            ),
            Data[Date]
        )
    VAR _nextNdate =
        MINX (
            FILTER (
                Data,
                Data[Customer] = EARLIER ( Data[Customer] )
                    && Data[Repeated call in 7 days? CC] = "N"
                    && Data[Date] > EARLIER ( Data[Date] )
            ),
            Data[Date]
        )
    VAR _tnextNdatenotblank =
        FILTER (
            Data,
            Data[Customer] = EARLIER ( Data[Customer] )
                && Data[Date] >= _startNdate
                && Data[Date] <= _nextNdate
                && Data[Date] <= EARLIER ( Data[Date] )
        )
    VAR _tnextNdateblank =
        FILTER (
            Data,
            Data[Customer] = EARLIER ( Data[Customer] )
                && Data[Date] >= _startNdate
                && Data[Date] <= EARLIER ( Data[Date] )
        )
    RETURN
        SWITCH (
            TRUE (),
            _nextNdate <> BLANK (), COUNTROWS ( _tnextNdatenotblank ),
            COUNTROWS ( _tnextNdateblank )
        )