Forum Discussion
Rolling Average Calculated Column for working days only
I'm hoping I can get some help.
I am building a report that is intended to show recent trends in store foot traffic based on rolling averages. There are three stores:
North is open 7/7 days a week
South is open 5/7 days a week
Central is open 4/7 days a week
My boss wants me to define the rolling 5 day average as the following:
Sum of customers for the last 5 weekdays divided by number of weekdays that store was open. This allows us to do an apples/apples comparison between stores. See example below:
| Day | North | South | Central |
Sunday | 2 | 0 | 0 |
| Monday | 3 | 3 | 6 |
| Tuesday | 6 | 4 | 7 |
| Wednesday | 4 | 1 | 10 |
| Thursday | 1 | 2 | 3 |
| Friday | 7 | 4 | 0 |
| Saturday | 5 | 0 | 0 |
| Sum of customers | 28 | 14 | 23 |
| Sum of weekday customers | 21 | 14 | 23 |
| Count of open weekdays | 5 | 5 | 4 |
| Rolling average | 4.2 | 2.8 | 5.8 |
I'm having a lot of trouble getting my Dax to work on this.
My goal is to show a line chart where dates are the axis, legend is location, and rolling average is the value.
Can anyone help?
Hi Anonymous ,
I am not sure your requirement, you could refer to my sample for details. If this is not waht you want, please correct me.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , you can have rolling 5 days like
Rolling 5 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-5,Day))
- parry2k
Anonymous you are missing core information in your model to perform such time intelligence functionality. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/Once you have a date dimension in your model, you can perform all kinds of Time intelligence calculations, and here is one video on rolling average and you can tweak it is as per your need. It is always good to learn best practices and follow that rather than jumping to the solution.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
daxCommunity Support
Hi Anonymous ,
I am not sure your requirement, you could refer to my sample for details. If this is not waht you want, please correct me.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
