Sum of last 3 rows

[JapieTeunissen]

Hi

In my mind I scowerd the web to find examples, but failed.

I need sum the last 3 rows.

The left column is the index/rowno.

Middle column the values.

I need a way to calc the last column: which is the sum of this row's value + the values of the last 2 rows.

(I can't get Sum and TOPN to work.)

Row	Value	SumOfLast3Values
1	5	5
2	10	15
3	15	30
4	20	45
5	25	60
6	30	75
7	35	90
8	40	105
9	45	120
10	50	135

Regards

[moizsherwani]

Hi @JapieTeunissen ,

I am sure there are multiple ways to do this, this was the first one which came to my mind. You need to create a measure which sums the "Value", second you need to create a rank of these SumValue and then you need to have a final running total which sums the rank of the last 3. See snippet, code and attached file

ValueTotal =
SUM ( SampleData[Value] )

ValueTotalRank =
RANKX ( ALL ( SampleData ), [ValueTotal],, ASC )

ValueTotalRunning =
VAR CurrentValue =
    SELECTEDVALUE ( SampleData[Value] )
VAR CurrentRank = [ValueTotalRank]
VAR CurrentRankMinusThree = CurrentRank - 2
RETURN
    CALCULATE (
        [ValueTotal],
        FILTER (
            ALL ( SampleData ),
            [ValueTotalRank] >= CurrentRankMinusThree
                && [ValueTotalRank] <= CurrentRank
        )
    )

[JapieTeunissen]

Both solutions worked perfectly.

I have 30k rows, so I get "not enough memory..." for both, but that is not your problem.

[johnt75 · Accepted solution]

You could do this either as a calculated column or as a measure, both have their advantaged and disadvantages. A measure would take account of any filters or slicers applied which a calculated column would not, but if it is a large dataset then a calculated column would likely give better performance.

To create a column you could use

Sum of last 3 values =
var currentRow = 'Table'[Row]
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
REMOVEFILTERS('Table'), 'Table'[Row] <= currentRow )

or as a measure

Sum of last 3 values =
var currentRow = SELECTEDVALUE('Table'[Row])
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
'Table'[Row] <= currentRow )

[JapieTeunissen]

I have 30k rows, so I get "not enough memory..." for both, but that is not your problem.