Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Folks
I have a Pivot that creates a Cohort Anlaysis as shown on the left below. This shows the number of customer remaining in a cohort in the subsequent months (invoice months) following signup. So for example in Jan 2016, the cohort started with 40 customers, and by March 2016 the number of customers was reduced to 22.
I want the date row (invoice Month) to be "indexed". So the first month the customer is invoiced to be 0, the second month to be 1, the third to be 3 (as shown on the right). In reality it doesn't matter if the index starts at 1 rather than 0. The link to the PowerPivot & associated DAX (that creates the % view) is here.
Any help or advice would would be much appreciated.
Steve
Solved! Go to Solution.
In this scenario, we can rank Invoice Month (from earliest month) within each Signup Month group, and use the rank number as the column group in Matrix. Please refer to following steps:
TotalCustomer = CALCULATE(COUNTROWS('Raw Cohort data'))
Inv_Year&Month = VALUE ( YEAR ( 'Raw Cohort data'[Invoice_Month] ) & "0" & MONTH ( 'Raw Cohort data'[Invoice_Month] ) )
RankInvoiceYear&Month = RANKX ( FILTER ( 'Raw Cohort data', EARLIER ( 'Raw Cohort data'[Signup Month] ) = 'Raw Cohort data'[Signup Month] ), 'Raw Cohort data'[Inv_Year&Month], , ASC, DENSE )
I did the exact process but I didn't get the results like yours, can I send you the file to check why not work?
@stfox If you are analyzing only 12 months at a time (1 Calendar Year) you could simply add a Calculated Column like so
DAX Index = MONTH ( 'Table'[Invoice_Month] ) - 1
Of course then you would have to adjust @OwenAuger's solution here
http://community.powerbi.com/t5/Desktop/Cohort-Analysis-DAX/m-p/40102#M15035
To something like this...
Measure DAX Index = DIVIDE ( [Customer ID Count], CALCULATE ( [Customer ID Count], GENERATE ( VALUES ( 'Table'[Signup Month] ), FILTER ( ALL ( 'Table'[DAX Index] ), 'Table'[DAX Index] = MONTH('Table'[Signup Month])-1 ) ) ) )
If you are however doing this on an ongoing basis - the Index would have to be constructed differently!
Thanks Sean, yes the hook is that the time period is greater than 12 months, and grows through time. Any suggestions ?
Cheers
Steve
In this scenario, we can rank Invoice Month (from earliest month) within each Signup Month group, and use the rank number as the column group in Matrix. Please refer to following steps:
TotalCustomer = CALCULATE(COUNTROWS('Raw Cohort data'))
Inv_Year&Month = VALUE ( YEAR ( 'Raw Cohort data'[Invoice_Month] ) & "0" & MONTH ( 'Raw Cohort data'[Invoice_Month] ) )
RankInvoiceYear&Month = RANKX ( FILTER ( 'Raw Cohort data', EARLIER ( 'Raw Cohort data'[Signup Month] ) = 'Raw Cohort data'[Signup Month] ), 'Raw Cohort data'[Inv_Year&Month], , ASC, DENSE )
Thank you so much! This has been really helpful!
Hello
I am posting here cos I have a similar issue. I simply want to rank my reporting date.
Reporting date Rank
1/21/2018 0
1/19/2018 -1
1/21/2018 0
1/17/2018 -2
Thanks Simon - Just what I was after. Totally logical - when broken down into those steps
Cheers Steve
BTW - The working example is here - for anyone who requires it in the future
What a great thread. Thanks Steve for posting your finished example. Cheers! Tom
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |