Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago
Solved

Cumulative Total

Hi,   I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, trying it as both a measu...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    10 years ago

    ElliotP Sorry about the original post. It was from my phone and had typos :smileywink:

     

    Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)

     

    Running Total COLUMN =
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
)

     

    And as you can see it works! :smileyhappy:

     

     

    And here's the MEASURE formula

     

    Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

     

    Which also works...

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello All,

 

Can someone please help me to calculate the running total please ?

I've tried several methods but unfortunately without luck 😞

 

I've an table with following columns:

  • issue/ defect reported date
  • Defect ID
  • Repaired (Y/N)
  • defect closure date

 

 

I would really appreciate, if anyone can help me on this.

 

Regards,

Jai

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Jai,

Can I suggest you try using the TOTALYTD function?

As you can see in my example below, it's quite straight forward for Sum functions so it should be quite straight forward for your count functions too. It also gives you the ability to set the year end date so that it groups the correct months together. That is the "31/3" in my example string below:

Order Intake Margin YTD Current Month = TOTALYTD([Total Order Intake Margin],CALENDAR[Report Date],"31/3")
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot, finally it worked 😀

    I've another measure to created with running total. For this, I've following columns:

    • Issue repair status (Y/N)
    • Issue closed date (available only if above column is "Y" otherwise blank)

    And for this, I would like to create a measure, to show the cumulative based on the month (i.e. how many issues has been closed on which month)

    Can you please help me this as well ?

    Regards,

    Jai