Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding rolling average to a cumulative total

Hi,   I have a measure which calculates the cumulative total. Now I need to calculate the average of this cumulative total (from October 2017 until June 2021) and add it to the last calculated cumu...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

     

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

I'm not getting clear from your description, it's better that you can show us some sample data and expected output for reference.

 

You can use the following measure to get average cumulative total

 

Measure =
AVERAGEX (
    SUMMARIZE (
        FILTER ( ALL ( Sales ), Sales[Date] < MIN ( Sales[Date] ) ),
        Sales[MonthYear],
        "rc",
            CALCULATE (
                [Sales Amount],
                FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date] <= MAX ( 'Sales'[Date] ) )
            )
    ),
    [rc]
)

 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-deddai1-msft 

    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).

     

     

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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