cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

HELP!!! Reversed cumulative total by group with criteria

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!

1 ACCEPTED SOLUTION
Resolver II

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!

3 REPLIES 3
Resolver II

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!

Frequent Visitor

Thanks for the help! It works great!!

Super User

Helpful resources

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors