Forum Discussion
Moving Average Across Consecutive 7 Days
Hello Experts,
I have below data:
Input:
Date FactValue
2020-07-21 5000
2020-07-22 2000
2020-07-23 2000
2020-07-24 1000
2020-07-25 1000
2020-07-26 4000
2020-07-27 6000
2020-07-28 1000
2020-07-29 5000
2020-07-30 5000
2020-07-31 2000
2020-08-01 5000
2020-08-02 5000
2020-08-03 2000
2020-08-04 11000
2020-08-05 9000
Expected Output
Date FactValue Average_Meaure_OF_FIRST_7_Days
2020-07-21 5000 3000
2020-07-22 2000 3000
2020-07-23 2000 3000
2020-07-24 1000 3000
2020-07-25 1000 3000
2020-07-26 4000 3000
2020-07-27 6000 3000
2020-07-28 1000 4000
2020-07-29 5000 4000
2020-07-30 5000 4000
2020-07-31 2000 4000
2020-08-01 5000 4000
2020-08-02 5000 4000
2020-08-03 5000 4000
2020-08-04 11000 10000
2020-08-05 9000 10000
I tried using DAX but unable to achieve above. I want to group first 7 dates then average the FactValue and show across all 7 days. Then again will take next 7 days and show average those 7 days. In last group I have only 2 days in that It should avg of 2days and Data will come incrementally so tomorrow will have data for 2020-08-06 , in that case Measure should show avg of 3 days.
Can we achieve this using DAX??
Thanks
Hi Anonymous
I build a table like yours to achieve your goal.
You can use M query to achieve your goal.
Firstly, add an index column in Power Query Editor
Then we add a custom column:
Then we add a Length column:
Add conditional Column , then fill up this column and remove other columns.
Result:
Build a measure to achieve your goal.
Avg = CALCULATE(AVERAGE('Table'[FactValue ]),FILTER(ALL('Table'),'Table'[Custom.1]=MAX('Table'[Custom.1])))Result:
You can download the pbix file from this link: Moving Average Across Consecutive 7 Days
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , try with a date table
Rolling 7 day = CALCULATE(sum(Table[FactValue]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))
or
Rolling 7 day = CALCULATE(sum(Table[FactValue]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-7,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi amitchandak ,
Thank You for the response.
Followed the approach mentioned with Date table but didnot get expected output. Measure shows the exact same vale for the FactValue. It does not show static average across 7 days and then for next 7 days.
Thanks
Hi Anonymous
I build a table like yours to achieve your goal.
You can use M query to achieve your goal.
Firstly, add an index column in Power Query Editor
Then we add a custom column:
Then we add a Length column:
Add conditional Column , then fill up this column and remove other columns.
Result:
Build a measure to achieve your goal.
Avg = CALCULATE(AVERAGE('Table'[FactValue ]),FILTER(ALL('Table'),'Table'[Custom.1]=MAX('Table'[Custom.1])))Result:
You can download the pbix file from this link: Moving Average Across Consecutive 7 Days
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
