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

     

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago

I've solved thef irst part using the EARLIER function

Moving Average = 
DIVIDE (
    CALCULATE (
        SUM ( [Cumulative Quantity1] ),
        FILTER (
            ALL ( 'All Web Site Data (2)' ),
            'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'All Web Site Data (2)'[Date] ),
        FILTER (
            ALL ( 'All Web Site Data (2)' ),
            'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
        )
    ),
    0
)

So it produces a literal moving average.

 

Now its a matter of creating a Measure to show YOY, Month on Month and day on day. This might be easier to break it into a few colums and calculate that way or use a Time Intelligent Function.

Sean's avatar
Sean
Community Champion
10 years ago

Okay great! Yes as you said - literal moving average because you didn't specify time period 7 Day, 1 Month, 6 weeks, 3 months...

 

For those you have to create the corresponding Running total first (which would again be your numerator)

say 3 months and then use something like this for the denominator

 

CALCULATE ( DISTINCTCOUNT(Calendar[Year-Month), DATESINPERIOD(CalendarTable[Date], LASTDATE(CalendarTable[Date]),-3,Month ) )

 

 

Yes I should have mentioned the Moving Average formula I posted was a Measure!

 

Here's the Column... :smileyhappy: