Hi, I am working on building a customer retention analysis.
Below is my sample data- New customer counts by year month.
Column "Periods" is a calculated column, breaking down those customers who joined at that year-month, and how many months they have been with us.
My main struggles are 2 points:
1. I would like to calculate the Reversed Running Total by each month as a group (decreasing by periods)
2. Due to the business logic, I would like to omit the latest 3 periods of each month group.
The preferred result is the most right column. And since most of the columns in my data are calculated columns, solutions via power query will not be ideal. Thank you in advance!
Go to Solution.
You can add a calculated column with this code.
VAR _period = 'Table (2)'[Period (month)]
VAR _latestPeriod = CALCULATE( MAX('Table (2)'[Period (month)]), ALLEXCEPT('Table (2)', 'Table (2)'[Year], 'Table (2)'[Month]))
IF( _period < _latestPeriod - 2, CALCULATE( SUM('Table (2)'[Count of Cus]), ALLEXCEPT('Table (2)', 'Table (2)'[Year], 'Table (2)'[Month]), 'Table (2)'[Period (month)] >= _period), BLANK())
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
View solution in original post
Thanks for the help! It works great!!
Please check this one Solved: Running total is reversed - Microsoft Power BI Community
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.