Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors