Sum a Running Total

[Sarah_]

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!

[Thennarasu_R]

@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_]

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. 

[amitchandak]

@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_]

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]

Hi,

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

[Sarah_]

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!

[Ashish_Mathur]

Upload the file to Google Drive and share the download link.

[Sarah_]

Test File 

Here's the data!

[Ashish_Mathur]

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_]

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]

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_]

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]

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_]

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]

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.