Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Year | Month | Period (month) | Count of Cus | |
2022 | January | 0 | 24 | 170 |
2022 | January | 1 | 14 | 146 |
2022 | January | 2 | 13 | 132 |
2022 | January | 3 | 9 | 119 |
2022 | January | 4 | 8 | 110 |
2022 | January | 5 | 5 | |
2022 | January | 6 | 12 | |
2022 | January | 7 | 85 | |
2022 | February | 0 | 16 | 118 |
2022 | February | 1 | 24 | 102 |
2022 | February | 2 | 10 | 78 |
2022 | February | 3 | 5 | 68 |
2022 | February | 4 | 3 | |
2022 | February | 5 | 5 | |
2022 | February | 6 | 55 | |
2022 | March | 0 | 26 | 137 |
2022 | March | 1 | 18 | 111 |
2022 | March | 2 | 15 | 93 |
2022 | March | 3 | 8 | |
2022 | March | 4 | 12 | |
2022 | March | 5 | 58 | |
2022 | April | 0 | 18 | 145 |
2022 | April | 1 | 25 | 127 |
2022 | April | 2 | 13 | |
2022 | April | 3 | 11 | |
2022 | April | 4 | 78 |
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!
Solved! Go to Solution.
Hi @jts_
You can add a calculated column with this code.
Result =
VAR _period = 'Table (2)'[Period (month)]
VAR _latestPeriod = CALCULATE( MAX('Table (2)'[Period (month)]), ALLEXCEPT('Table (2)', 'Table (2)'[Year], 'Table (2)'[Month]))
RETURN
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())
Cheers
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
Hi @jts_
You can add a calculated column with this code.
Result =
VAR _period = 'Table (2)'[Period (month)]
VAR _latestPeriod = CALCULATE( MAX('Table (2)'[Period (month)]), ALLEXCEPT('Table (2)', 'Table (2)'[Year], 'Table (2)'[Month]))
RETURN
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())
Cheers
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
Thanks for the help! It works great!!
Please check this one Solved: Running total is reversed - Microsoft Power BI Community
Thanks,
Ritesh
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.