Cohort Analysis

[sguenther]

Hello,

I would like to create a cohort analysis, showing the activity of users (grouped by signup month) over time. Therefore I could use the custom Heat-map visualization. The only thing I need is a table showing the number logins by user groups for each month. I was able to set this up in excel, but I'm relatively new to DAX, so I'm not really sure how to do this.

What I have is a table with all logins and the the user signup date (transformed to startofmonth) for each login (and created an additional column (cohort) which gives me the cohort number: 1 (for Jan 14) - 23 (for Nov 15). I set up a custom table with all the cohorts and the start dates of each cohort. I also created another seperate date table and linked all three via date. I set up a measure:

Activity per Month= CALCULATE(COUNTA('activity'[login]), FILTER(ALL(Dates[Date]), AND((YEAR(Dates[Date]) = MAX(Dates[Year])) , (MONTH(Dates[Date]) = MAX(Dates[Month])))))

This gives me the number of logins per Month. But what i need is the number of logins for each cohort. My idea was to set up a measure for each cohort. But I don't know how to set up the filter correctly. I think that I need to filter in the date table and the activity table (for the cohort). Something like this:

Activity per Month cohort 1= CALCULATE(COUNTA('activity'[login]), FILTER('activity'[login] , 'activity'[cohort] =  1) && FILTER(ALL(Dates[Date]), AND((YEAR(Dates[Date]) = MAX(Dates[Year])) , (MONTH(Dates[Date]) = MAX(Dates[Month])))))

But of course this doesn't work. Any ideas?

[Greg_Deckler]

These types of questions are far easier to answer if you can post some sample data and what your desired output is for that sample data. In general, I would not recommend trying to filter by individual cohort. In theory, you could use your current measure and simply create a table with cohort and that measure and your measure should be context filtered to give you a count of logins by cohort.

[sguenther]

@Greg_Deckler: thx for the quick reply. Unfortunately I can't share my data here due to confidentiality reasons. But I could share a mockup of my data if needed. But actually this is just a DAX related question. I also think that I should be able to use my current measure, but I need to insert this additional filter correctly. And I just don't knwo how to do it in DAX. Basically what I need to do is to add a Filter which filters the argument of my COUNTA function.

[Greg_Deckler · Accepted solution]

You could try:

Activity per Month cohort 1= CALCULATE(COUNTAX('activity'[login], 'activity'[cohort] =  1), AND((YEAR(Dates[Date]) = MAX(Dates[Year])) , (MONTH(Dates[Date]) = MAX(Dates[Month]))))

Might have an extra paren in there. Can't be sure unless I have some mock data to build a data model with and test.

[GershwinMunich]

i am also having issues on how to start with cohort analysis on power bi.

Greg, can you assist?