cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Cumulative Sums - Cohort Analysis

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). 

 

Simon17652_0-1598429223415.png

Simon17652_1-1598429428793.png

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Simon17652 

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. 

Solution1.png

PFA the pbix file also.Download PBIX file 

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

@Simon17652 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

Highlighted

@Simon17652 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.

 

 


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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!

Highlighted

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

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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... 🙂

Highlighted

Hi @Simon17652 

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. 

Solution1.png

PFA the pbix file also.Download PBIX file 

View solution in original post

Highlighted

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 )

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors