Cumulative Total aggregating prior years

[kkiii]

I want to draw a line chart for this, Year in the X axis, Cumulative total in Y axis.

The assumption is that the Cumulative total only starts to count in the year after and the saving is aggregating and repeated each year after

For 2019, the toal is 0.

For 2020, the total is 10 from 2019. 

For 2021, the total is 10*2+100=120.

For 2022, the total is 10*3+100*2+1000=1230.

Year	Time Saving
2019	10
2020	100
2021	1000

Could anyone help?

[barritown · Accepted solution]

Hi @kkiii,

If you can add "2022" to your initial table, the following calculated column will do the calculation:

In plain text:

Result = 
VAR CurrentYear = [Year]
VAR tbl = ADDCOLUMNS ( FILTER ( Data, [Year] < CurrentYear ), "Ratio", CurrentYear - [Year] )
RETURN SUMX ( tbl, [Ratio] * [Time Saving] ) + 0

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian