Accumulate data ignoring select dimension

[#1]

Hello,

I am trying to make a graph and a table which shows at any date what the EOD accumulated balance is. I am able to make the data accumulate but only when all dates are visible.

E.g. When no filter is on the date dimension the EOD balances are correct.

Date	Value	Accumulated value (EOD balance)
1	100	100
2	200	300
3	300	600
4	400	1,000

However when I filter to dates 3 and 4 the accumulation starts from 0.

Date	Value	Accumulated value (EOD balance)	EOD balance should be
3	300	300	600
4	400	700	1,000

The syntax I am using is the following

rangesum( above(

sum({$<[Segment 1]={'00'}, [Segment 2]={'00'}, [Segment 3]={'999'}, [Nominal Code]={'7100'}, OriginatingCurrency={'USD'}, LineRef -= {'Balance Brought Forward'}>}([OriginatingDebit]-[OriginatingCredit])/1000000)

,0,rowno()))

[#2]

I've attached some dummy data - will this work?

Edit: realised later you needed the expected output - I've added a second sheet with two scenarios: no filter and a filter on 7th and 8th Dec

[sunny_talwar · Accepted solution]

Here you go...

When not selected

When Selected

Expression

RangeSum(Above(

Sum({$< [Segment 1] = {'0'},

  [Segment 2] = {'0'},

        [Segment 3] = {'999'},

        [Nominal Code] = {'7100'},

        OriginatingCurrency = {'USD'},

        LineRef -= {'Balance Brought Forward'},

        [TRX Date]>} ([OriginatingDebit]-[OriginatingCrebit])) , 0, RowNo()))

*

Avg({$< [Segment 1] = {'0'},

  [Segment 2] = {'0'},

        [Segment 3] = {'999'},

        [Nominal Code] = {'7100'},

        OriginatingCurrency = {'USD'},

        LineRef -= {'Balance Brought Forward'}>} 1)