Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to calculate a rolling total

 I need to get a rolling total by month for the Result below. Can someone show me how to accomplish this. I'm currently using a slicer for the dates.

         ValueA                ValueB         Result

Jan         5                        2                 7

Feb        3                        1                 11

Mar       4                        1                 16

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    A calendar table is needed for the model.

    And please create a new measure like:

    Result = 
CALCULATE (
    SUM ( 'TableA'[ValueA] ) + SUM ( 'TableB'[ValueB] ),
    'Calendar'[Month Num] <= MAX ( 'Calendar'[Month Num] ),
    REMOVEFILTERS ( 'Calendar'[Month] )
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

11 Replies

  • kpost's avatar
    kpost
    Icon for Solution Sage rankSolution Sage
    2 years ago

     

    I produced this table using only the first 3 columns.   Your question is too vague to give specific advice, but:

     

    BIG PICTURE, you need to create a measure that changes filter context, specifically around the date value.

    I've done it in a Power BI file, attached,  with the following measure.  The exact structure of this is could be wildly different depending on your situation (no information provided about your data model, no sample data, no .pbix file etc), however, again, BIG PICTURE, you want to modify the filter context.

    Result =
    VAR max_rowdate = max('Table'[Date])
    RETURN
    CALCULATE(COALESCE(SUM('Table'[ValueA]) + SUM('Table'[ValueB]), 0), ALL('Table'), 'Table'[Date] <= max_rowdate)

    See attached .pbix file.  Let me know if you have any questions.  Good luck!

    ///Mediocre Power BI Advice, but it's free///
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I'm getting this error. Percentcol is a calculated column.

       

       

      • kpost's avatar
        kpost
        Icon for Solution Sage rankSolution Sage
        2 years ago

        With more information about your data tables and model, I could be much more helpful.

        If you could create a sample .pbix file re-creating the issue you are experiencing, that would be the absolute best case scenario.  Way less back-and-forth, and plus i can just upload a .pbix file containing the solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    A calendar table is needed for the model.

    And please create a new measure like:

    Result = 
CALCULATE (
    SUM ( 'TableA'[ValueA] ) + SUM ( 'TableB'[ValueB] ),
    'Calendar'[Month Num] <= MAX ( 'Calendar'[Month Num] ),
    REMOVEFILTERS ( 'Calendar'[Month] )
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group