Cohort Analysis on monthly base with all data in one table

[ppalacin]

Hi folks,

I am strugeling with this for the last hours. 

I try to achieve to do a cohort analysis of my phone calls according to this post:

http://community.powerbi.com/t5/Desktop/Want-to-perform-Cohort-Analysis/td-p/172834

In difference to that I have all data in one table which is resulting in 0 output

My table basically looks like this:

Call ID	Caller ID	Call Time
732	4705	2/14/2017 17:22
733	3572	2/14/2017 17:27
734	4722	2/14/2017 17:30
735	4256	2/14/2017 17:33
736	4718	3/14/2017 17:45
737	4722	3/14/2017 17:46
738	4718	3/14/2017 17:48
739	4734	3/14/2017 17:53
17402	4705	4/14/2017 17:55
74134	4705	6/14/2017 17:56

What I want now is to group all callers by month to one cohort. Let's they everybody that has called in February is part of cohort 20172 cohort = (CONCATENATE('consultation'[created_at].[Jahr], 'consultation'[created_at].[MonthNo]))

I would like now to have a resulting matrix that tells me how many of each monthly cohort have called in the month after e.g.

Cohort	March 2017	April 2017
20172	3 (8%)	4 (10%)

I would really appreciate any idea or advice (+ explanation) how to set this up.

I was able to do it in SQL but I am really struggeling to achieve this with DAX

Best

Patrick

[Ashish_Mathur]

Hi,

Why is the answer for March 2017 3?  There is only 1 Caller ID from Feb which also called in March - Caller ID 4722.  Likewise, the answer for April should also be 1 - Caller ID 4705.

Please clarify.

[ppalacin]

Ah sorry,

thats my bad.

I have just cutted the table before posting.

@CheenuSing

Looks very promising, will try it and give feedback.

Thanks a lot!

[v-huizhn-msft]

Hi @ppalacin,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @ppalacin,

I create a calculated column to get YearMonth in the sample table using the formula below.

YearMonth = YEAR(Table1[Call Time])&MONTH(Table1[Call Time])

Then create a measure to group all callers by group. Create a table to display the result.

Cohort = IF(HASONEVALUE(Table1[YearMonth]),CONCATENATEX(Table1,Table1[Caller ID],","))

>>Next, you would like now to have a resulting matrix that tells me how many of each monthly cohort have called in the month after e.g.

Cohort	March 2017	April 2017
20172	3 (8%)	4 (10%)

Actually, I can't get your requirement here, how to calculate the 2(8%)? Please share the rules of calculation in the sample table, so that we can post solution which is close to your needs.

Best Regards,
Angelia

[ppalacin]

Hi Angelia,

thanks for your great help. I have to ask to share the python code I am currently using for calculation but I try to explain it quickly.

My table shows me how many callers of a cohort have called again in the specifiy month.

Let's say I have 37 distinct people that did call me in February => cohort 20172

3 out of those 37 have called again in March (~8%)

4 out of those 37 in April (~10%)

That's what I need to calculate for every cohort with a 6 month scope.

Hope now it's clear what I try to achieve. I have still not figured out how to do it with DAX, right now I have to do everything programmticly in python.

Best Patrick

*Side note: The 3 callers in march would be also part of the cohort "20173" together with all others calling in that month, the 4 in april part of "20174" and so on 😉

[CheenuSing]

Hi @ppalacin

I am treating your requirement as finding the repeat callers in the last six months. 

Try the following

1. Create a summarized table

   FirstCalled = SUMMARIZE(Caller,Caller[CallerID],"FirstCall",MIN(Caller[CallDate]))

    This builds a list of CallerID with the corresponding first call date.

   If you already have a separate CallerID dimension table, add the column to compute the first called date and populate the same.

2. This table needs to be joined to your Caller table on CallerID.

3. I hope you have a calendar table based on the min and max value of CallDate of the Caller table.

4. Link the calendar date column to CallDate column in caller table.

5. Create a measure called

    TotalCallers = DistinctCount(Caller[CallerID])

6. Create a measure called repeat callers in the last six months

RepeatCallers =
CALCULATE (DISTINCTCOUNT(Caller[CallerID]),
FILTER (
ADDCOLUMNS(
SUMMARIZE ( Caller, [CallerID],  [CallDate],"FCALL", VALUES(FirstCalled[FirstCall])),
"LostMonths", DATEDIFF(  [FCALL],[CallDate] ,MONTH) ) ,
[LostMonths]>= 1 && [LostMonths] <= 6 )

)

7. Creaate measure called

RepeatCallers% = Divide([RepeatCallers],[TotalCallers])

8. Now plot the table visual

Sample output with the data provided by you. Column cohort is as provided @v-huizhn-msft.

If you can provide more data I can test it out and send back.

If this works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[GershwinMunich]

i am trying to figure out the same thing and the firstcalled function doesnt work because The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[CheenuSing]

Hi @GershwinMunich

The firstcalled is not a function it is a a summarized table

   FirstCalled = SUMMARIZE(Caller,Caller[CallerID],"FirstCall",MIN(Caller[CallDate]))

Please go through my posting at the beginning and follow on the same lines step by step.

Let me know if you need further help.

Cheers

CheenuSing