DAX Measure to calculate number of calls within 30 days from first call date

[SubodhPatra]

Hi,

I need a measure to calculate number of calls within 30 days from the first call date for each user who have "call type" as CALLBACK.

I am referring to calling data and the sample data is attached. Table name is "fact call" and want to calculate the number of users who called again within 30-days from the first call date for Apr2021 & May 2021.

Callerid              Date            call_type
912222222222 01-03-2021 CALLBACK
919999999999 01-03-2021 CALLBACK
913333333333 06-03-2021 CALLBACK
914444444444 11-03-2021 CALLBACK
912222222222 11-03-2021 CALLBACK
915555555555 16-03-2021 CALLBACK
913333333333 16-03-2021 CALLBACK
916666666666 21-03-2021 CALLBACK
914444444444 21-03-2021 CALLBACK
915555555555 26-03-2021 CALLBACK
916666666666 31-03-2021 CALLBACK
919999999999 05-04-2021 CALLBACK
912222222222 15-04-2021 CALLBACK
919999999999 15-04-2021 CALLBACK
913333333333 20-04-2021 CALLBACK
914444444444 25-04-2021 CALLBACK
912222222222 25-04-2021 OBD
915555555555 30-04-2021 CALLBACK
913333333333 30-04-2021 OBD
916666666666 05-05-2021 CALLBACK
914444444444 05-05-2021 OBD
915555555555 10-05-2021 OBD
916666666666 15-05-2021 OBD

[Anonymous]

Hi @SubodhPatra ,

Please refer to my pbix file to see if it helps you.

Create a column first.

Column =
VAR _MINDATE =
    CALCULATE (
        MIN ( 'Table'[Date  ] ),
        FILTER (
            ( 'Table' ),
            'Table'[call_type] = "CALLBACK"
                && 'Table'[Callerid ] = EARLIER ( 'Table'[Callerid ] )
        )
    )
VAR AMONTH = _MINDATE + 30
VAR _result =
    IF ( ( 'Table'[Date  ] ) <= AMONTH, 1, BLANK () )
RETURN
    _result

Then create a measure.

Measure =
CALCULATE (
    COUNT ( 'Table'[Column] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Callerid ] = SELECTEDVALUE ( 'Table'[Callerid ] )
            && 'Table'[Column] = 1
    )
)

If I have misunderstood your meaning, please provide more details with your desired output and more sample data.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SubodhPatra]

Hi,

Apologize for the late reply as I was a bit ill.

My requirement was to calculate unique users (mobile numbers) by Month who had called within 30 days from their first call.

The result would be

Month               # of New Users who called again within 30 days from first call date

Mar-2021               5

Apr-2021               0

May-2021              0

For Mobile Number 919999999999, the calling difference from first call(01-03-2021) is more than 30 days and hence would not be counted in Mar-2021 as well as Apr-2021.

[Anonymous]

I hope this time it will be ok

nb call general =

var datemin=min('fact call'[Date ])

var datemaxi=datemin+30

var _step1=ADDCOLUMNS(

summarize('fact call','fact call'[Callerid ]),

"@Nbcall",CALCULATE(count('fact call'[Callerid ]),'fact call'[Date ]>=datemin&&'fact call'[Date ]<=datemaxi&&'fact call'[call_type]="CALLBACK")

)

var _step2=countrows(filter(_step1,[@nbcall]>1))

return

_step2

[SubodhPatra]

Hi James,

Thanks a lot for the DAX measure.

The shared measure works fine for users who called multiple times in a particular month within 30 days irrespective of first call date to my calling platform.

Requirement :

• Number of new users who called atleast once more within 30 days from their first callback

So need to calculate 30 days from the first calling date (CALLBACK) for each user (mobile number) from the entire calling data and then count multiple calling within 30 days from their first calling date.

I tried to get first calling date as below but could not be able to calculate 30 days from this date for all users

First_Date = CALCULATE(MIN('Fact Calls'[date]), VALUES('Fact Calls'[callerid]),

FILTER(ALL('Fact Calls'), 'Fact Calls'[call_type] = "CALLBACK")

)

Sorry to bother you again and again.

[Anonymous]

Hi

The final result you are looking for is something like this ?

[SubodhPatra]

Hi James,

Logic is the format you shared but the output required is as below 

Month             # of New Users who called again within 30 days from first call date

Mar-2021                2

Apr-2021                5

May-2021              12

etc.    

[Anonymous]

SubodhPatra

Sorry I do not understand how you calculate the result of 2 in March!

[SubodhPatra]

Hi,

These numbers are tentative figures, not actual as per sample data shared.

Sorry for the inconvenience.

[SubodhPatra]

Hi James,
As per sample data shared, the calling dates for call_type "CALLBACK" are as below

Mob Number       Call-1             Call-2              Call-3
912222222222    01-03-2021    11-03-2021    15-04-2021
913333333333    06-03-2021    16-03-2021    20-04-2021
914444444444    11-03-2021    21-03-2021    25-04-2021
915555555555    16-03-2021    26-03-2021    30-04-2021
916666666666    21-03-2021    31-03-2021    05-05-2021
919999999999    01-03-2021    05-04-2021    15-04-2021

The result would be

Month               # of New Users who called again within 30 days from first call date

Mar-2021               5

Apr-2021               0

May-2021              0

For Mobile Number 919999999999, the calling difference from first call(01-03-2021) is more than 30 days and hence would not be counted in Mar-2021 as well as Apr-2021.

[Anonymous]

Hi SubodhPatra,

I create a new column in the table

After just use is in the report

You can use filter for just to see the "Yes"

Hope this helps

[Anonymous]

Hi

just create this measure :

nb call general =
VAR mindate =
    MIN ( 'fact call'[Date ] )
VAR maxdate =
    MAX ( 'fact call'[Date ] )
VAR _step1 =
    ADDCOLUMNS (
        SUMMARIZE ( 'fact call', 'fact call'[Callerid ] ),
        "@Nbcall",
            CALCULATE (
                COUNT ( 'fact call'[Callerid ] ),
                'fact call'[Date ] >= mindate
                    && 'fact call'[Date ] <= maxdate
            )
    )
VAR _step2 =
    COUNTROWS ( FILTER ( _step1, [@nbcall] > 1 ) )
RETURN
    _step2

here is the result

Have a nice day

[SubodhPatra]

Hi James,

Thank you very much for the measure.

It is picking minimum and maximum date but I need within 30 days from first calling date of each user (phone number) and call type should be "CALLBACK".

Also, I need to calculate the users count who called again within 30 days from the first calling date. 

Please help.
Thanks in advance!!!