Forum Discussion

Frequent Visitor

3 years ago

Sum a Running Total

Hi, I am trying to get a running 3 year total that then also sums for the quarter. So what I want is something like this - Where I can calculate a running total, and then sum that total on a q...

Super User

3 years ago

Sarah_ , You can get qtr value with help from date tbale and time intellignece , same for all month in qtr

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

or

This Qtr=
var _max1 = today()
var _min = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,mod(Month(_max1),3)))+1
var _max= eomonth(_min, 2)
return
if('Date'[Date]>= _min && 'Date'[Date] <= _max , "Latest Qtr" format([Date], "YYYY-QQ") )

Sarah_
Frequent Visitor
3 years ago
I am so appreciative of your help! But I need to do more than QTD sales. I have a 3 year running total by month, and want the quarter to be the sum of the 3 months' running value. So if Oct running total value is 50, Nov has sales of 10 which makes a running total of 60, and Dec again has sales of 10 so the running total is 70, then I want the Q4 total to = 50+60+70. But I can't sum the running total. It just gives the last month total.
- Ashish_Mathur
  Super User
  3 years ago
  Hi,
  
  Share some data to work with and show the expected result very clearly.
  - Sarah_
    Frequent Visitor
    3 years ago
    Hi Ashish,
    
    I created an excel file with fake data to send, but I can't seem to upload it. Below is a new screenshot using the fake data, along with a description of what I see vs what I want to see.
    Here is the formula I am using for the 3 year running total:
    
    3 Year Running Total =
    CALCULATE(SUM(Sheet1[Quantity]),
    FILTER(ALLEXCEPT(Sheet1,Sheet1[Description Detail]),
    Sheet1[Calendar Date] > MAX(Sheet1[Calendar Date])-1080 &&
    Sheet1[Calendar Date] <= MAX(Sheet1[Calendar Date])))
    
    Is there a way to upload an excel file here?
    
    Thank you!