The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Greetings.
I am trying to get 25 days Rolling Average based on the data which has only YearWeek and StartDayofWeek as date columns. The requirement is that the 25 days Rolling Average should be calulated at diffrenent Dimension Columns selected in table.
CalendarMonth, ObjectID, DimColumnA, DimColumnB, DimColumnC, DimColumnE, DimColumnC, AverageRollingSum
I tried with Many Dax, but not giving me the expected rolling Average, always gives the SUM as result. Not really sure if its beacuse of the data. below is the Sample data. Values is the column that needs to be considered for rolling Average.
YearWeek | StartDayOfWeek | ObjectID | CalWeek | CalendarMonth | Values | DimColumnA | DimColumnB | DimColumnC | DimColumnE | DimColumnC |
202401 | 1/1/2024 | 11 | 202401 | 202401 | 10.00 | Time001 | LMM901 | NN | POS | |
202401 | 1/1/2024 | 11 | 202401 | 202401 | 0 | Time001 | KML00 | MM | ||
202401 | 1/1/2024 | 11 | 202401 | 202401 | 20.00 | Time002 | N9001 | MM | ||
202401 | 1/1/2024 | 15 | 202401 | 202401 | 0 | Time002 | KO | ABC102 | MM | |
202402 | 8/1/2024 | 11 | 202402 | 202402 | 10.00 | Time001 | LMM901 | |||
202402 | 8/1/2024 | 11 | 202402 | 202402 | 20.00 | Time001 | KML00 | |||
202402 | 8/1/2024 | 11 | 202402 | 202402 | 0 | Time0003 | KML00 | |||
202402 | 8/1/2024 | 16 | 202402 | 202402 | 0 | Time0003 | KML00 | |||
202402 | 8/1/2024 | 15 | 202402 | 202402 | 10.20 | Time002 | ABC102 | |||
202403 | 15/1/2024 | 11 | 202403 | 202403 | 0 | Time001 | LMM901 | |||
202403 | 15/1/2024 | 11 | 202403 | 202403 | 12.09 | Time001 | KML00 | |||
202403 | 15/1/2024 | 11 | 202403 | 202403 | 0 | Time0003 | KML00 | |||
202403 | 15/1/2024 | 11 | 202403 | 202403 | 15.10 | Time002 | N9001 | |||
202403 | 15/1/2024 | 15 | 202403 | 202403 | 0 | Time002 | ABC102 | |||
202404 | 22/1/2024 | 11 | 202404 | 202404 | 0 | Time001 | LMM901 | OL | ||
202404 | 22/1/2024 | 11 | 202404 | 202404 | 10.00 | Time001 | KML00 | MM | ||
202404 | 22/1/2024 | 11 | 202404 | 202404 | 06 | Time002 | KO | N9001 | MM | |
202404 | 22/1/2024 | 15 | 202404 | 202404 | 0 | Time002 | ABC102 | MM | ||
202405 | 29/1/2024 | 11 | 202405 | 202405 | 20.00 | Time001 | LMM901 | |||
202405 | 29/1/2024 | 11 | 202405 | 202405 | 0 | Time001 | KML00 | |||
202405 | 29/1/2024 | 11 | 202405 | 202405 | 0 | Time0003 | KML00 | |||
202405 | 29/1/2024 | 16 | 202405 | 202405 | 0 | Time0003 | KML00 | |||
202405 | 29/1/2024 | 15 | 202405 | 202405 | 0 | Time002 | ABC102 | |||
202406 | 5/2/2024 | 11 | 202406 | 202406 | 0 | Time001 | LMM901 | |||
202406 | 5/2/2024 | 11 | 202406 | 202406 | 10.00 | Time001 | KML00 | |||
202406 | 5/2/2024 | 11 | 202406 | 202406 | 0 | Time0003 | KO | KML00 | ||
202406 | 5/2/2024 | 11 | 202406 | 202406 | 0 | Time002 | N9001 | |||
202406 | 5/2/2024 | 15 | 202406 | 202406 | 0 | Time002 | ABC102 |
I don't see a way to find 25 day rolling average, can be 2,3,4,5... Week rolling average.
Can you share pbix file with sample data/mockup data and end result for comparison.
Hi talespin,
As i am a recent users, not able to upload files. But Below are the sample data.
The requirement is when i have YearWeek column in report it should do 12 weeks rolling average at Week level, When i add DimColumnA it should then recalculate 12 weeks rolling average at DimColumnA. and when DimColumnB is added it t should recalculate 12 weeks rolling average at DimColumnB.
@PowerBi_1404
You can perform rolling average using DAX window function,
here is a example from SQL BI
https://www.sqlbi.com/articles/introducing-window-functions-in-dax/
Or if you are using latest version of Power Bi desktop then you can use visual calculations: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
74 | |
31 | |
30 | |
24 | |
21 |