Caculated column: Sum previous 3 rows

[Jyu1994]

Dear all, 

I have this set of data, I need to add a calculated column summing "Mvs" from the previous 3 rows. Any idea?

FFor example, the new columns shoul look like this: 

Index	Mvs	NewColumn
1	122
2	103
3	182
4	170	407
5	130	455
6	106	482

[Shahid12523 · Accepted solution]

SumPrev3Mvs =
VAR i = 'YourTable'[Index]
RETURN
CALCULATE(
SUM('YourTable'[Mvs]),
FILTER('YourTable', 'YourTable'[Index] >= i - 3 && 'YourTable'[Index] < i)
)

Adds a column that sums Mvs from the previous 3 rows based on Index.
Works great for sequential data like yours.

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(Data[ndex]>=4,CALCULATE(SUM(Data[Mvs]),FILTER(Data,Data[ndex]>=EARLIER(Data[ndex])-3&&Data[ndex]<=EARLIER(Data[ndex])-1)),BLANK())

Hope this helps.

[MarcoSparkBI]

@Jyu1994 , Hi, here is another way i can provide, you can do it in power query.

load your data or open your power query editor.

you can put below M code:

= Table.AddColumn(#"Changed Type", "Prev3Mvs", each
if [Index] >= 4 then
List.Sum(
Table.SelectRows(#"Changed Type", (x) => x[Index] >= [Index]-3 and x[Index] < [Index])[Mvs]
)
else
null
)

then you will get your results.

But Honestly, to add caculate columns in data sets to calculate based on rows, this is not the best practice. as your dataset going bigeer, you will have performance issue. that's the reason i recomend to you do it in back ground if necessary in powerquery. 
and also it is possible to do just by measure, if you want to display in a visual in power bi. while, i think above can already fullfil your requirement. if you need more method. we can discuss

best regards

Marco

[GeraldGEmerick]

@Jyu1994 While I don't generally recommend calculated columns in DAX, you could do it this way:

Column = 
VAR _Index = [Index]
VAR _MinIndex = _Index - 3
VAR _Result = IF( _MinIndex < 1, BLANK(), CALCULATE( SUM( 'Table'[Mvs] ), FILTER( 'Table', [Index] <= _Index - 1 && [Index] >= _MinIndex ) ) )
RETURN _Result