Forum Discussion

SuperCal99's avatar
SuperCal99
Helper I
4 years ago
Solved

Running Total with Categories

Hi,

 

I would like to create a running total which not only counts per day totals but also per category.
I would then like to create a line graph which i can then filter.

 

Thanks in advance for any assitance

Need Help
  • danextian's avatar
    danextian
    4 years ago

    Also:

    Please update your sample data so it matches the numbers on the visuals you have posted.

    What is the format of your date - dd/mm/yy or mm/dd/yy?

    And is the year supposed to be 2022 while your date is 2021?

    For running total based on week number, you can try this formula:

    Running (Week) = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table'[Weeks Closing] ),
        'Table'[Weeks Closing] <= MAX ( 'Table'[Weeks Closing] )
    )
)

     

     

9 Replies

  • SuperCal99's avatar
    SuperCal99
    Helper I
    4 years ago

    Apologies, I also want to also create another cumulative line graph but with a "weeks to closing date" value in the y axis, so a "weeknumber to closing date" column also exists in the table above as well.

     

    • danextian's avatar
      danextian
      Super User
      4 years ago

      Hi SuperCal99 ,

       

      There are many ways to create a running total but for your use case a simple TOTALYTD measure will do.

      RunningTotal =
TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date Registration] )

       

      For your other requirements, can you please update your sample data so it will be easier for the community to come up with a solution rather than us coming up with a mocked up data first?  Please also include expected results and the logic behind weeks to closing, etc.

      • SuperCal99's avatar
        SuperCal99
        Helper I
        4 years ago

        Hi danextian 

         

        Thanks for your response
        The YTD measure doesnt appear to help, i cant seem to be able to insert it into the X axis. Only the Y-axis.

        In reference to my other requirement, I have updated my data table to reflect the "Weeks to closing".

        Many thanks

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi SuperCal99 ,

     

    Why do you delete your original post? Do you mean that the problem has been solved?

     

    If the problem has been solved, please keep your post to help the other members with the same questions find it more quickly.

     

    If the problem hasn't been solved, please also keep your post and others can help you.

     

     

    Best Regards,

    Icey