3 Replies Latest reply on May 14, 2018 11:15 PM by Zhouyi Zhang

# Previous Month Running Total With Fixed LOD?

Hi all,

Not sure if this is possible, but figured here is the best place to ask.

I have a calculation that gives me the previous month's running costs and makes it available to calculations in the current month. There's 2 calculated fields...

Prior Month Cost:

If

DATEDIFF("month",[Field Date],NOW())=1

Then

([Total Cost])

END

Prior Month Running Total:

RUNNING_SUM(SUM([Prior Month Cost])) <-Computed at Table: Across

So when I have that in a view it tells me the total spend for (assuming today is 15th May) April in both the April and May LOD. I can then also do calculations on that field so that May is manipulated. You can see in the attached that the sum keeps increasing as the month goes on, then reaches May and fixes its result for the entire month of May.

However, what I'd like to do is fix that result so that rather than using a running sum I fix the LOD to State and Month, so that the value returned is always the state total no matter what LOD I'm using. That is, if I add in a lower level of detail, say City, it will still show the total cost for April in that state. My colleague and I have tried a few different ways of doing this, mainly with FIXED or INCLUDE, and while we can get it to sum up the state total for April we can't seem to make that value available to May.

So to use the attached example, I want to return 45,659,837 no matter what level of detail I'm using.

Is that possible?

Cheers,

Bob

• ###### 1. Re: Previous Month Running Total With Fixed LOD?

Hi, Rob

Do you have any sample data to share?

ZZ

• ###### 2. Re: Previous Month Running Total With Fixed LOD?

Hi ZZ,

I've cleaned up some of the "real" data to hopefully illustrate what I'm trying to do. Essentially the figure at "State" is what I'm trying to fix, so that regardless of the LOD (e.g. Region) it'll still show the "State" figure (e.g. 198635 would display for NSW Metro / NSW Rural / Other on the "Region" sheet)

Fingers crossed I've attached it correctly as well!

Cheers,

Rob

• ###### 3. Re: Previous Month Running Total With Fixed LOD?

Hi, Rob

Thanks for the workbook, really helpful

Please find my solution attached and below result

Hope this helps

ZZ

1 of 1 people found this helpful