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 customer phone number)

3) "Repeated calls in 7 days?" - calculated columnt that puts "Y" in row if this is a repeated call (there was a call from the same "Customer ID" within period of 7 days before "Date") , if not - it puts "N".

4) Call number - this is the calculated column i am struggling with..I'd like to assign consecutive number for each call if it happens from the same customer ID within the period of 7 days. If the previous call from id happened earlier - the count starts again from "1".

DateCustomer IdRepeated calls in 7  Days ?Call number
24-11-231111111N1
25-12-231111111N1
30-12-221111111Y2
06-01-241111111Y3
02-02-241111111N1
04-02-241111111Y2
09-02-241111111Y3



Your help will be much appreciated !

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    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 )
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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 )
    )

     

  • Dimi_2207's avatar
    Dimi_2207
    Helper I
    2 years ago

    Thank you very much ! This is exactly what i was looking for !