cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cumulative measure with condition and specific window size (unbounded preceding to current_row-1)

Hi all,

 

I have some project data as follows:

Sichanm_1-1616782875184.png

I have made a star schema from the attributes that I need for processing such as asset type and precinct.

 

For the financial analysis, I have selected some of the columns that are required and unpivoted the Fys data to appear in the rows (not columns) as shown below.

 

Sichanm_0-1616784535296.png

 

 

Fy is the year part of Fys and cashflow is the value of that column.

Fys stands for the financial year and the Grand Total is the sum of all Fys.

 

Now, I need to calculate commitment. I have added a column in the above example to show how it is calculated. The logic is as follows:

Problem:

For the first year, it is equal to the sum of all the Fys (which we can get from Grand total rather than summing up all Fys). For any other year (i.e. second, third,...), commitment is equal to the Grand total - (sum of the cash flow of all the previous years). Therefore,

For the first year it is qual to Grand Total i.e. 1500.

For the second year, it is Grand total - Cash flow first year (i.e. Fy first year), i.e. 1500-100=1400.

For the third year, it is Grand Total - (Fy first year +Fy second year), i.e. 1500- (100+200)=1200. 

and so on

 

Since these projects may have different start dates and end dates and they may happen in different years, I have removed those Fys which are null and calculated the rank for the remaining based on the Fys . For example, for the above example, all the Fys from 2025 to 2050 are null, so I have removed them as they are not shown in the unpivoted table and they are ranked based on the value of Fys in ascending order.

 

My rank formula:

 

Rank = CALCULATE(
COUNTROWS('DPO Portfolio_FY'),
FILTER(
ALL('DPO Portfolio_FY'),
'DPO Portfolio_FY'[Id] = EARLIER('DPO Portfolio_FY'[Id]) &&
'DPO Portfolio_FY'[FY_Start] < EARLIER('DPO Portfolio_FY'[FY_Start])
)
)+1
 
Happy to use Power Query or DAX or any other possible way
 
Numerical Example:
IDPKAsset typePrecinctRankFYCashflowActual CommitmentGrand Total
1100WaterA1202010015001500
1100WaterA2202120014001500
1100WaterA3202230012001500
1100WaterA420234009001500
1100WaterA520245005001500
2200TransportA1201920010001000
2200TransportA220203008001000
2200TransportA320214005001000
2200TransportA420221009001000

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have to use a calculated column as it is a row by row calc.

 

This link helped me a lot 

 

Comm_calc1 =CALCULATE (sum ('DPO Portfolio_FY'[Cash Flow]),Filter(ALLEXCEPT('DPO Portfolio_FY','DPO Portfolio_FY'[Id]),'DPO Portfolio_FY'[Rank]<EARLIER('DPO Portfolio_FY'[Rank])))
 
and then 
 
Commitment =  if('DPO Portfolio_FY'[Rank]==1,'DPO Portfolio_FY'[Grand Total],'DPO Portfolio_FY'[Grand Total]-'DPO Portfolio_FY'[Comm_calc1])

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I have to use a calculated column as it is a row by row calc.

 

This link helped me a lot 

 

Comm_calc1 =CALCULATE (sum ('DPO Portfolio_FY'[Cash Flow]),Filter(ALLEXCEPT('DPO Portfolio_FY','DPO Portfolio_FY'[Id]),'DPO Portfolio_FY'[Rank]<EARLIER('DPO Portfolio_FY'[Rank])))
 
and then 
 
Commitment =  if('DPO Portfolio_FY'[Rank]==1,'DPO Portfolio_FY'[Grand Total],'DPO Portfolio_FY'[Grand Total]-'DPO Portfolio_FY'[Comm_calc1])
Anonymous
Not applicable

Not clear. Please refer to this guide: How to Get Your Question Answered Quickly (powerbi.com)

 

Anonymous
Not applicable

Hi @Anonymous , thanks for the comment, I have revised it to make it easier to understand and hopefully clear this time. 

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!

Thank you 2022 Review

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