Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |