Forum Discussion
Cumulative Sums - Cohort Analysis
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
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... 🙂
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
- Posting the DAX for the measure... thanks again Gopa
Cumulative Cohort =------------------------------------------------------------------------//find the cohort of the sales personsVAR _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 contextVAR _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 clientVAR __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 periodVAR __clients1 =FILTER (__CustMasterTable1,[@FirstApplication] IN __rlvntcalDateTable&& [@salesman] IN __rlvntsalespersons&& 'Client Master'[Churned Date] > _contextmaxdate)------------------------------------------------------------------------RETURNCOUNTROWS ( __clients1 )
