cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## 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!

Helper I

Thanks for the help! It works great!!

Super User

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors