Forum Discussion
Adding rolling average to a cumulative total
Hi Anonymous ,
First, you should add an index column in your table, and then you can use the following calculated column:
Cumulative Expenditure = VAR A = CALCULATE ( SUM ( 'Table'[Expenditure] ), FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) ) VAR B = 'Table'[Index] - CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Expenditure] ) ) ) ) RETURN IF ( ISBLANK ( 'Table'[Expenditure] ), A + B * AVERAGE ( 'Table'[Expenditure] ), A )For more details, please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks for your reply!
What I'm trying to achieve is the below.
I want to calculate the yellow cells based on the cumulative total adding the newly calculated average (of course the average will remain the same). So I need a new measure to calculate the cumulative amount from 2022 and onwards using the previous value and the average. It needs to be a new measure since the old one is used as a starting point for a forecast (the line from January 2022 will be dotted in Power BI. I have a forecast measure which is mirroring the cumulative total until dec 2021 and then it should split up using the new cumulative).
Community SupportHi Anonymous ,
First, you should add an index column in your table, and then you can use the following calculated column:
Cumulative Expenditure =
VAR A =
CALCULATE (
SUM ( 'Table'[Expenditure] ),
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
)
VAR B =
'Table'[Index]
- CALCULATE (
MAX ( 'Table'[Index] ),
FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Expenditure] ) ) )
)
RETURN
IF (
ISBLANK ( 'Table'[Expenditure] ),
A
+ B * AVERAGE ( 'Table'[Expenditure] ),
A
)
For more details, please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai