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

    Previous Month Running Total With Fixed LOD?

    Rob Bissonnette

      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