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 guys,
i've seen many examples for a rolling sum when using months or weeks or days. But my data does not contain any of these. I simply have a column with numbers and I want to keep a running sum of, say the last 3 rows for that measure. Example:
Index Measure Runing sum
1 1 1
2 3 4
3 2 6
4 4 9
I've tried that code in a new column called Rolling Sum:
Solved! Go to Solution.
Try this as a Measure.
Thanks for your input.
I tried your code on 100 rows and it returns empty cells for each row. Not sure why. I checked HASONEVALUE and it seems to always return FALSE, could explain why i get empty cells ?
I also tried to run it on my entire table (490'000 rows x 17 columns) and it says "working on it..." but never finishes after 30 min
thanks for your input,
If I keep the 2 MAX words I get always the same value for each row, which is the sum of the last 3 rows of my measure column.
If I remove the 2 MAX words then it works as I want. But it seems quite slow. I managed to test it on 100 rows, but I never got to the end of the calculation when running it on my full table (490'000 rows by 17 columns), it says "working on it..." even after 1 hour.
edit: I actually used your code for a new column. If I use it in a measure, then it works fine and requires the 2 MAX words. I still need to check if it is fast enough for my 490'000 rows
Mine did not use MAX. I think you have the 2 responses mixed. No matter.
My DAX query was meant to be a measure. If you tried it as a Calculated Column, it probably was slow due to the context switching. Take some time to learn about the DAX Filter and Row Contexts. When you have a column calculation, you are running in a row context, but if your formula uses CALCULATE, it transitions the row context into a filter context. This is an expensive operation and thus adds some time to your expression.
When adding columns to your source data, it is best to consider your source queries. For example, if you are sourcing SQL Server and have control of the query that pulls the data, you can add some joins to get your results. Below is a crude example of a query. You maybe could use something fancier like LAG or a CTE with recursion, but this gives you a quick and understandable solution.
SELECT t1.Index, t2.Value,
t1.Value + COALESCE(t2.Value, 0) + COALESCE(t3.Value, 0) AS SumOfLast3
FROM dbo.MyTable AS t1
LEFT OUTER JOIN dbo.MyTable AS t2 ON t1.Index = t2.Index + 1
LEFT OUTER JOIN dbo.MyTable AS t3 ON t1.Index = t3.Index + 2;
sorry, I inverted your user names above.
Indeed your code works if I use it in a measure and on 100 rows.
Now i've got to let it run on a larger number of rows to see how fast it goes.
will revert.
Sorry, slight fix to query.
SELECT t1.Index, t1.Value,
t1.Value + COALESCE(t2.Value, 0) + COALESCE(t3.Value, 0) AS SumOfLast3
FROM dbo.MyTable AS t1
LEFT OUTER JOIN dbo.MyTable AS t2 ON t1.Index = t2.Index + 1
LEFT OUTER JOIN dbo.MyTable AS t3 ON t1.Index = t3.Index + 2;
Try this as a Measure.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |