Forum Discussion
Cumulative Total aggregating prior years
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?
Hi Anonymous,
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] ) + 0Best Regards,
Alexander
1 Reply
- barritown
Solution Sage
Hi Anonymous,
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] ) + 0Best Regards,
Alexander