Cumulative Sums - Cohort Analysis

[Anonymous]

Hello PowerBI Community, 

I have managed to pull the raw data I need and have begun plotting on a matrix table for a cohort analysis. The issue I'm facing is that I cannot seem to work out how to make the figures cumulative... i.e February 2017 should be 3 in month 1, 3 in month 2, 3 in month 4 etc..... then 5 in month 13 etc. In essence, the values are counting the amount of user_ids joining at a particular month. Each Month/Year is the cohort upon which new sales members joined the team (we have had new people every month). 

So far I think I have this.... but by partitioning by cohort, I'm puzzled about the filters... 
Cumulative Values = 

CALCULATE ( 

        SUM((DISTINCTCOUNT('Cohorting Table'[user id])

        ),

        FILTER (

                     cohort = x, month >= y????

Thanks!

@dsabsi  @amitchandak  - might you be able to help?

@Greg_Deckler  - I saw you made a simple to understand calc here - https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-Running-Total/m-p/1080236 - I'm struggling to use it though!

[amitchandak]

@Anonymous , Need some data.

No month on top should be diff what. Today() - Start Date or first Date and Start date (it is column name )

I have retails data

First Sales = minx(filter(Retail, Retail[Customer_id]=EARLIER(Retail[Customer_id])),Retail[Order_Date])
Month Since first Sales = DATEDIFF(Retail[First Sales],Retail[Order_Date],MONTH)

Now I join first sales with date table take month -year on row

Join the second with series and take on column

Month Of Sales = ADDCOLUMNS(GENERATESERIES(0,100), "Month Seq", "Month" & [Value])

In case we need a measure bucketing , Assume Age is my measure

Age bucket Count = CALCULATE(COUNTX(filter(VALUES(Retail[Customer_id]), [Age] =max('Month Of Sales'[Month No])),Retail[Customer_id]),CROSSFILTER('Month Of Sales'[Month No],Retail[Month Since first Sales],None))

crossfilter is option

also refer

https://finance-bi.com/power-bi-cohort-analysis/
http://funbiworld.com/2019-12-02-cohort-analysis/
https://www.youtube.com/watch?v=tuk1CrigTSI

[Anonymous]

Hi @amitchandak , 

Thanks for your reply - what specific data do you need? 

Well the month/year tell me what month the sales cohort onboarded a client. So in February 2017, the sales cohort onboarded 3 clients in the first month and then in their 13th month, they onboarded a further 2 clients. It goes all the way to 42 months (as thats the month difference from Today() to the sales joined date. 

I'm not sure I understand your workings in that case!

S

[Greg_Deckler]

@Anonymous Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi @Greg_Deckler , 

Many thanks.

1) Please find sample data below in the URL: https://docs.google.com/spreadsheets/d/1PTz8ElfVvYAfxbudrMSXHEn_j1_33rtv8P4dU3CsNGE/edit?usp=sharing  

2) The expected output is mentioned in the original post. 

3) I currently have a table with the cohort of sales people (month and year) on the left and the month that they have been with us on the top (0-42). I have already plotted the (distinct) count of user_id's on each month, however I would like to make these numbers cumulative. This is because the cohort analysis table will show us how many sales to date they have made throughout their time with us. 

I hope this makes sense and thanks again for your time in responding. 

@amitchandak  - does the above help? Thanks!

[amitchandak]

@Anonymous , I will check that. I have given a webinar on a similar topic. 

refer: https://youtu.be/Q1vPWmfI25o?t=752

[Anonymous]

Thanks @amitchandak  - I have created a date table and made a join. This is my calculation so far for the values:

Distinct Count of Clients =
CALCULATE (
    DISTINCTCOUNT ( 'Cohorting Table'[Client User ID] ),
    USERELATIONSHIP ( _Dates[Date], 'Cohorting Table'[First Salesman Application Completed Date] )
)

The trick now is to make it cumulative... 🙂

[Gopa · Accepted solution]

Hi @Anonymous 

Please see if the following helps you. As part of the solution, I have made some data transformations, created relationships, and a measure. All these changes can be seen in the pbix. I have used the excel files you had send me. So, you can just change the file path of the source files, should you wish to refresh or explore the query editor part. 

PFA the pbix file also.Download PBIX file 

[Harsh_Joshi]

Hello @Gopa,

The PBIX file doesn't exist. Can you please help us by re-uploading the PBIX file?

[Anonymous]

Posting the DAX for the measure... thanks again @Gopa 

Cumulative Cohort =

------------------------------------------------------------------------

//find the cohort of the sales persons

VAR _rlvntmonthyear =

SELECTEDVALUE ( 'Dates Data'[MonthYear] )

VAR _rlvntcohortperiod =

SELECTEDVALUE ( 'Cohort Months'[No] )

VAR __rlvntDateTable =

CALCULATETABLE (

VALUES ( 'Dates Data'[Date] ),

'Dates Data'[MonthYear] = _rlvntmonthyear

)

VAR __rlvntsalespersons =

CALCULATETABLE (

VALUES ( 'Cohorting Data'[Salesman ID] ),

TREATAS (

__rlvntDateTable,

'Cohorting Data'[First Salesman Application Completed Date]

)

) ------------------------------------------------------------------------

--find all the dates below the current max date in context

VAR _periodmaxdate =

CALCULATE ( MAX ( 'Dates Data'[Date] ), __rlvntDateTable )

VAR _contextmaxdate =

EOMONTH ( _periodmaxdate, _rlvntcohortperiod )

VAR __rlvntcalDateTable =

CALCULATETABLE (

VALUES ( 'Dates Data'[Date] ),

ALL ( 'Dates Data' ),

'Dates Data'[Date] <= _contextmaxdate

)

------------------------------------------------------------------------

--add a column to the Client Master table with the first application and the salesman for that client

VAR __CustMasterTable1 =

ADDCOLUMNS (

'Client Master',

"@FirstApplication",

CALCULATE (

MIN ( 'Cohorting Data'[Application Completed Date] ),

'Cohorting Data'[Client User ID] IN VALUES ( 'Client Master'[Client User ID] )

),

"@salesman",

CALCULATE (

MIN ( 'Salesman Master'[Salesman ID] ),

CROSSFILTER ( 'Cohorting Data'[Salesman ID], 'Salesman Master'[Salesman ID], BOTH )

)

)

------------------------------------------------------------------------

--filter the clients only for those who are under the cohort salesmen and their first application is on or before the current end of month in context and who has not churned during the period

VAR __clients1 =

FILTER (

__CustMasterTable1,

[@FirstApplication] IN __rlvntcalDateTable

&& [@salesman] IN __rlvntsalespersons

&& 'Client Master'[Churned Date] > _contextmaxdate

)

------------------------------------------------------------------------

RETURN

COUNTROWS ( __clients1 )