Unit store sales - How to First count daily, Second Rolling Average?

[qwer123]

Hi Everyone,

I need to calculate rolling average of unit gross sales, which equal to Daily Sales/Restaurant Count.

I expected the rolling average = average of each daily unit gross sales, which take account of different restaurant count every day. Using the pic as an example, I expect the rolling average on 3 Nov 2021 would be equal to (2494+2604+2766)/3 = 2621. However, what it runs out is = 178110/3/23=2581. May I get your advice how to modify the code? Thanks much in advance.

Rolling 30 days unit gross sales =
var sum_30days = calculate([Unit gross sales], datesinperiod(table1[Deliver Date],max(table1[Deliver Date]),-30,DAY))
var days_in_30period = calculate(DISTINCTCOUNT(table1[Deliver Date]),datesinperiod(table1[Deliver Date],max(table1[Deliver Date]),-30,DAY))
return
sum_30days/days_in_30period

PBIX file for ref
https://drive.google.com/file/d/1yNUHlifEvVjarD9OawCYkMhk0YT6O6_N/view?usp=share_link

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[qwer123]

Here is PBIX file.
https://drive.google.com/file/d/1yNUHlifEvVjarD9OawCYkMhk0YT6O6_N/view?usp=share_link

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[qwer123]

Thanks Ashish. May I ask one more question - if I aim to calculate rolling average by category (e.g. restaurant ID), how could I modify the code so the table can show the correct number by restaurant ID by date?

[Ashish_Mathur]

You are welcome.  Share some data and show the expected result.