topic Re: DAX Measure to calculate number of calls within 30 days from first call date in Desktop

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

SubodhPatra — Mon, 16 May 2022 08:53:18 GMT

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

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

Anonymous — Mon, 16 May 2022 09:18:58 GMT

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

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

SubodhPatra — Mon, 16 May 2022 10:58:23 GMT

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

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

Anonymous — Mon, 16 May 2022 13:44:53 GMT

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

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

SubodhPatra — Tue, 17 May 2022 07:53:43 GMT

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.

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

Anonymous — Tue, 17 May 2022 08:57:52 GMT

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

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

SubodhPatra — Tue, 17 May 2022 09:14:09 GMT

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.

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

Anonymous — Tue, 17 May 2022 11:40:53 GMT

SubodhPatra

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

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

SubodhPatra — Tue, 17 May 2022 11:47:57 GMT

Hi,

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

Sorry for the inconvenience.

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

SubodhPatra — Tue, 17 May 2022 12:55:38 GMT

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.

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

Anonymous — Thu, 19 May 2022 07:18:43 GMT

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.

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

Anonymous — Thu, 19 May 2022 08:45:05 GMT

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

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

SubodhPatra — Mon, 23 May 2022 11:33:12 GMT

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.