cancel
Showing results for
Did you mean:
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:

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.

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:
 ID PK Asset type Precinct Rank FY Cashflow Actual Commitment Grand Total 1 100 Water A 1 2020 100 1500 1500 1 100 Water A 2 2021 200 1400 1500 1 100 Water A 3 2022 300 1200 1500 1 100 Water A 4 2023 400 900 1500 1 100 Water A 5 2024 500 500 1500 2 200 Transport A 1 2019 200 1000 1000 2 200 Transport A 2 2020 300 800 1000 2 200 Transport A 3 2021 400 500 1000 2 200 Transport A 4 2022 100 900 1000

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])
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

Anonymous
Not applicable

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

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