Forum Discussion
Rolling sum
Hi ,
I am looking for a Power BI formula for rolling Sum.
Example: i have 50 Row line items , I need create a rolling sum for every 3 Points .Rolling sum of last 3 transactions (May occur in a given time interval, Day or a week)...Rolling sum consecutive 3 Transactions.
Sum( Row1 : Row 3)
Sum( Row 2:Row 4)
Sum( Row 3: Row5)
..
..
Sum( Row48:Row50)
This is very easy to create in excel . But how do we create this in Power BI,This is not in context month or a year
Hi smshetty ,
You may go to Query Editor, go to Add column->Index column (from 1,,), click button "Close & Apply".
Then you may create measure like DAX below.
Rolling 3 rows = VAR i= SELECTEDVALUE(Table1[Index]) RETURN CALCULATE (SUM ( Table1[transactions value] ),FILTER ( ALLSELECTED(Table1), Table1[Index] <= i + 2 && Table1[Index] >= i ) )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi smshetty ,
You may go to Query Editor, go to Add column->Index column (from 1,,), click button "Close & Apply".
Then you may create measure like DAX below.
Rolling 3 rows = VAR i= SELECTEDVALUE(Table1[Index]) RETURN CALCULATE (SUM ( Table1[transactions value] ),FILTER ( ALLSELECTED(Table1), Table1[Index] <= i + 2 && Table1[Index] >= i ) )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks, this was very useful for me too, is there a way to find the max value of the function or would you need to create a new column?
Hi smshetty
Do you need this in a calculated column or in a measure? IF the latter, how will that measure be used (what visual, what fiedls on the visual)?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
