Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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 |
Solved! Go to 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.
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.
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.
@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
@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
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the September 2025 Power BI update to learn about new features.