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 q...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

In another tab of that Excel workbook, please show the expected result.  The image you posted earlier is very small so I cannot understand what youw want.

  • Sarah_'s avatar
    Sarah_
    Frequent Visitor
    3 years ago

    Apologies for the delayed response - I just put a picture of the desired result in the second tab of the data sheet. Thank you! 

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Pasting the screenshot in another tab of the Excel workbook just does not help.  How have you derivd the figures in column C of the PBI screenshot worksheet?  Explain that.

  • Sarah_'s avatar
    Sarah_
    Frequent Visitor
    3 years ago

    In the screenshot I'm trying to show what I am getting vs what I want. 

     

    I am able to acheive a monthly 3-year running total using the following formula:

     

    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])))
     
    So the running total by month is just fine. However, the running total is not accurate on a quarterly basis. So in the screenshot, Columns C and F are the same value even though Column C is for the month and Column F is for the quarter. In Column G I typed what I actually want Column F to show. Since Column F is a quarterly number, it should be summing the results of the 3 year running total for the quarter. 
     
    Thanks!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    I am not interested in seing your formula - I'll write my own.  To receive any further help from me, please answer the question that i have asked in my prevous message.

  • Sarah_'s avatar
    Sarah_
    Frequent Visitor
    3 years ago

    I'm really sorry, I thought I was answering your questions. I'm very grateful for the help. Could you maybe re-phrase the question as I clearly didn't understand. I thought you were asking me about data in the screenshot that appeared in Column C, which was from the formula I created. What should I be clarifying instead? 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Please show which MS Excel formula you would have written to calculate the numbers in clumn C of the PBI screenshot worksheet.  Show the formula in the cells of that column.