how to get cumulative sum from previous values in the same column

[jack10063]

Hi,

I am trying to create a calculated column that calculates cumulative sum from previous values in the same column. On a spreadsheet I can do it pretty easily as below:


value_date portfolio_number first_value diff_to_target nominal_new
1.11.2015 4 TRUE 10 10
2.11.2015 4 FALSE 15 5
3.11.2015 4 FALSE 20 5
4.11.2015 4 FALSE 25 5
5.11.2015 4 FALSE 30 5
6.11.2015 4 FALSE 35 5
7.11.2015 4 FALSE 40 5
8.11.2015 4 FALSE 45 5
9.11.2015 4 FALSE 50 5

and the formula on spreadsheet is:

=IF(C4=TRUE;D4;D4-SUMIF($B$2:B3;B4;$E$2:E3))CODE]


So, I would like get nominal_new as calculated column in PowerPivot. For that I need to look diff_to_target at current row and then deduct sum of nominal_new from previous rows in nominal_new column. So, for example for value_date 3.11.2015 nominal new would be 20 (diff_to_target) - 15 (nominal_new 1.11.2015=10+nominal_new 2.11.2015=5) = 5.

For that I tried to create a formula: [CODE]=[diff_to_target]-CALCULATE(SUM(data[nominal_new]);FILTER(ALLEXCEPT(data;data[portfolio_number]);data[value_date]<EARLIER(data[value_date])))

However, PowerPivot doesn't accept that formula because of circular dependency. I was thinking this should be an easy one to build but cant' figure it out. Thanks for any help!

br,
Jack

 

[jack10063]

For some reason the DAX formula isn't complete in the previous post. It is continuing as: "EARLIER(data[value_date])))" and "<" before EARLIER but for some reason I can't post it here correctly!

 

[Ozeroth]

Hi Jack,

As you've observed, a calculated column can't refer to itself.

You could avoid circularity by defining [nominal_new] = [diff_to_target] current row - [diff_to_target] previous row.

There are a few ways to do this but here is one possibility:

=
data[diff_to_target]
    - CALCULATE (
        MAX (data[diff_to_target] ),
        CALCULATETABLE (
            LASTDATE ( data[value_date] ),
            FILTER ( data, data[value_date] < EARLIER ( data[value_date] ) )
        ),
        ALLEXCEPT ( data, data[portfolio_number])
)

(This assumes value_date is data type Date.)

Does this give the right result?

 

[jack10063]

That works nicely, thanks a lot for your help!

br,
Jack