Forum Discussion
Cumulative Total
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...
Thank you so much, we're making progress.
I've tried it as a measure as well as a new calculated column, yet it shows the cumulative total in each row; photos to demonstrate;
https://gyazo.com/0d365fcaaba2507bca2dffe1177837eb
https://gyazo.com/37b810f7b9f4659492b405b3362106db
As well, what do you mean in regards to date field date table. Should I set it to another table type?
For a calculated column :
CumulativeQuantity2 =
VAR CURRENTDATE='All Web Site Data (2) '[Date]
RETURN
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2)');'All Web Site Data (2) '[Date]<= CURRENTDATE))
For a measure:
CumulativeQuantity-M =
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2) ');'All Web Site Data (2) '[Date]<= MAX('All Web Site Data (2) '[Date])))
I hope this help you.
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...
Sean What if we have multiple rows for same date?
Thank you so much guys, I really appreciate it. It has been doing my head in.
Why do we use the filter feature for the measure but not the column? I checked and the measure formula works for a new column as well, but I'm curious as to explanation of the difference.
As well, I know this is going to be all the more complicated; But I'd also like to calculate a moving and trailing average. I'll have a try myself again, but if either Sean or Vvelarde knows the forumla off the top of their head, that would be greatly appreciated.
Thank you so much.ElliotP Okay since you didn't mention how many Days or Month Average
Try this...
Moving Average = DIVIDE ( 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] ) ) ), CALCULATE ( DISTINCTCOUNT ( 'All Web Site Data (2)'[Date] ), FILTER ( ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] ) ) ), 0 )The Numerator is basically your Running Total Measure (so you actually can use the Measure name there) while
The Denominator is the number of days.
See picture to see how formula works :smileyhappy:
If i have a continuos data of the above kind, how will i calculate the culmative sum based on Month, Quarter and Year.
Thanks,
Raaghavan
