Rolling Average Calculated Column for working days only

[738o51]

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.

pbix here

Can anyone help?

[dax · Accepted solution]

Hi @738o51 , 

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@738o51 , 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]

@738o51 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-ca...

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.⚡