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 cumulative total (June 2021), then ricalculate the average (from October 2017 until July 2021) and add it to the new cumulative total (July 2021) and so on (needs to be iterative).

My date column is spanning from October 2017 until June 2021, now I want to see the trend for upcoming months up until the sales amount reaches the target.

 

Below the formula for the cumulative total:

CALCULATE ( [Sales Amount],

    FILTER ( ALLSELECTED ('Sales'),

        'Sales'[Date] <= MAX ( 'Sales'[Date] )

        )

    )

 

Thanks!

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

     

3 Replies

  • 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