Need an Expression for Full Accumulation/Cumulative in a Pivot table

[#1]

I have a Table with a Cost Type Column containing: Obligated, Actual's and Projected across a Month Dimension ranging from Jan-2012 into Feb-2013 a rolling calendar so YTD wont be useful. I just need a 'simple' Cumulative running total based off my set analysis sample below.

I need get have a Cumulative summation from Cost type Obligated & Actual.  See Image below for ref.

The problem is Full accumulation only works in a Chart not a table. I'm new at this so I need a good starting point.

My Measure is : Amount

The Expression I use to display the Obligated Columns is: Sum({$<[Cost Type]={"Obligated*"}>}Amount)

This works fine. But if I want to compare my Actuals against my Obligated Funds which may not be in that Month then its shows I ran out of Funds even though they were allocated in Months past. The end goal is to color code the areas were the Cumulative is < less than the Cumulative Actual's for that month then flag it red.

Cumulative Obligated < Cumulative Actual's Then make Color background Red.

Thank you for any assistance rendered.

Brian

[whiteline]

Oh, thats simple, use:

below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0

[#3]

Makes sense. For some reason though it went all solid.

[whiteline]

I've tried it before post.

If(below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 AND Sum({$<[Cost Type]={"Actuals*"},$(vLastMonth)={"<$(vNextMonth)"}>}Amount) <= 0,'<S2>','<S1>')

[#5]

Ok, that did work. I just had to insert it properly in my If statement. Awesome such a simple and elegant solution.

I really need to start knowing the Chart Inter Record functions!

Thanks So much this one has been bugging me for three days.

[#6]

This one did the trick....

If(Below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 ,'<S2>','<S1>')

[#7]

This is the one that works:  If(Below(Sum({$<[Cost Type]={"Actual*"}>}Amount)) <= 0 ,'<S2>','<S1>')

The one with vMonths was my failed logic I was playing with.  the 'below' function is such a better method maybe the only method!

[whiteline]

Thank you, 'cause I've met that several times before and today I've finally realised how to beat that

[#9]

I have a dilemma, I'm trying to solve on my pivot table. My Cumulative is showing data 2 months past where it should? See below.... 

My Actual Cumulative should stop at September but it keeps going along with my Obligated which should not either.  Any pointers?  Is this expected behavoir? See attached Image

RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo()))

Thanks!

[whiteline]

Yes its expected.

For every column after September your expression can be calculated clearly. And its logically right. There is no actual after September so the result is the same but it exists.

You have to add a condition to make it zero in future if you want, for example:

Count({1<[Cost Type]={"Actual*"}>}Amount) > 0

[#11]

Ok, that makes sense now. I had a feeling that would be the case. So I need to nest the Count expression you provided into my RangeSum it sounds like?

Or do I use an If statement and use the count to qualify the Rangesum before its used?

=If( Count({1<[Cost Type]={"Actual*"}>}Amount) > 0 , RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo())) , 0 )