Forum Discussion

Jyu1994's avatar
Jyu1994
Regular Visitor
1 year ago
Solved

Caculated column: Sum previous 3 rows

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...
Need Help
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    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.

MarcoSparkBI's avatar
MarcoSparkBI
Frequent Visitor
1 year ago

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