Forum Discussion

Sarah_'s avatar
Sarah_
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 quarterly basis. 

 

I found the below solution for creating a 3 year running total on a monthly basis, but that measure doesn't get summed for the quarter. The value displayed for the quarter is the exact same as the month. Any ideas why my running total won't sum? Thank you!

 

 

Need Help

15 Replies

  • amitchandak's avatar
    amitchandak
    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_'s avatar
      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's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Share some data to work with and show the expected result very clearly.

  • Thennarasu_R's avatar
    Thennarasu_R
    Responsive Resident
    3 years ago

    Sarah_ 
    Try This measure,

    Measure = CALCULATE ([Test1],
    FILTER (
    ALLSELECTED( 'Calendar_New'),
    'Calendar_New'[sortyearmonth]<=MAX('Calendar_New'[sortyearmonth])
    && 'Calendar_New'[FY_Year] = MAX ('Calendar_New'[FY_Year])
    )
    )

    Thanks
    Thennarasu
     
    • Sarah_'s avatar
      Sarah_
      Frequent Visitor
      3 years ago

      Thanks Thennarasu! I tried a modified version - I couldn't figure out how the [sortyearmonth] and [FY_Year] portions of the formula were working so I went with this - 

       

      3 Year Running Total =
      CALCULATE(SUM(Sheet1[Quantity]),
      FILTER(ALLSELECTED(Sheet1[Calendar Date]),
      Sheet1[Calendar Date] > MAX(Sheet1[Calendar Date])-1080 &&
      Sheet1[Calendar Date] <= MAX(Sheet1[Calendar Date])))
       
      It gives me the same result where I get a monthly running total, but it doesn't sum the values for the quarter.