Custom rolling average formula help

[Anonymous]

Hi Power BI community,

I am having an impossible time creating a measure I need.

The goal is to create a rolling average of customer volume with these parameters:

- Numerator = number of customers who came into the store during the last 5 open days (excludes customers who came in on closed days)

- Denominator = number of days that I had customer volume during the last 5 open days (excludes days with customer volume if the store was closed)

Using this, I want to create a rolling average accross my three stores. This rolling average should be calculated for each store and then added together. In other words, the sum of each store's rolling average should equal the sum of the stores' rolling averages.

Finally, I need these in a measure format, because I will begin to add filters to my customer volume measure (age, gender, loyalty program member, etc).

I've created an excel file that captures the rolling average line and a Power BI that has the data.

Can anyone please help?

[amitchandak]

@Anonymous , refer if this blog to travel across working days can help you

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[Anonymous]

Thanks! This is helpful in validating my logic for some other measures, but unfortunately it doesn't help with my rolling average problem (at least that I can see).