Serial number based on condition in DAX

[Dimi_2207]

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

Date	Customer Id	Repeated calls in 7  Days ?	Call number
24-11-23	1111111	N	1
25-12-23	1111111	N	1
30-12-22	1111111	Y	2
06-01-24	1111111	Y	3
02-02-24	1111111	N	1
04-02-24	1111111	Y	2
09-02-24	1111111	Y	3

Your help will be much appreciated !

[Dimi_2207]

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

[Jihwan_Kim · Accepted solution]

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