cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jts_
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.

 

YearMonthPeriod (month)Count of Cus 
2022January024170
2022January114146
2022January213132
2022January39119
2022January48110
2022January55 
2022January612 
2022January785 
2022February016118
2022February124102
2022February21078
2022February3568
2022February43 
2022February55 
2022February655 
2022March026137
2022March118111
2022March21593
2022March38 
2022March412 
2022March558 
2022April018145
2022April125127
2022April213 
2022April311 
2022April478 

84851683-9e1a-43cd-bf32-7851543316ac.png

 

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
liuqi_pbi
Resolver II
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())

liuqi_pbi_0-1660035040835.png

 

Cheers

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

View solution in original post

3 REPLIES 3
liuqi_pbi
Resolver II
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())

liuqi_pbi_0-1660035040835.png

 

Cheers

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

Thanks for the help! It works great!!

ribisht17
Super User
Super User

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.