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

[Anonymous]

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

[Anonymous · Accepted solution]

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 clear. Please refer to this guide: How to Get Your Question Answered Quickly (powerbi.com)

[Anonymous]

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