14-days rolling avarage of Sales (excluding days with no sales -> weekends and holidays)

[Anonymous]

Hello together

My Power BI contains sales figures from 05/30 to 09/30/2022 for 40 customers at 6 different locations. The Power BI contains three datasets (3 static and 1 dynamic):

• Static Data – Date: Date table
• Static Data – Location: Location table
• Static Data – Customer: Customer table
• Dynamic Data – Sales: Sales figures broken down to all the static varibales and here is also the Sales rolling 14-day average (variable name: Sales Rolling Average 14 calculated)

I am trying to calculate a rolling 14-day (However, the measure should be valid for any number of days) average of sales per day in the following data set. The programmed rolling 14-day average (see below) works in principle.

However, I am struggling with the following problem:

• I don't have sales on weekend and national holidays.
• The date table includes weekends and holidays, because if not PowerBI does not recognize the date table as date table.
• The formula must understand that if there is no sale on a day, the day is not reported and not used to calculate the rolling average. For example if there is a a weekend in between, it does not have to go back 14 days, but 16 days. If there are two weekends in between, the formula would have to go back 4 more days, so 18 days. It depends from where on calculates the rolling average. In addition, the formula must understand that if there is one holiday in between it must go back one more day.
• For instance the 14 day rolling average on 20.06.2022 (should go back to 31.05.2022) should show a value of 18'027.4. However, my formula shows 18'709.4 because it only goes back to 07.06.2022 since it does not exclude holidays and weekends. 04.06, 05.06, 11.06, 12.06, 18.06 and 19.06 are weekends. 06.06.2022 is a holiday. You can see this below:

I have already tried to extend and modify the formula, but none leads to the solution.

The Power BI is in the link below so that you can see the data structure for yourself:

https://drive.google.com/file/d/1T5wr-OFZtnlr-q8XTvgj7VEupkwpfdgq/view?usp=share_link

I would be very grateful for your help, because I really do not know what to do.

Kind regards

[IIPowerBlog]

hello @Anonymous  , in your Dataset (or database) are the sales on Weekends 0 or null? 

There is a simple way to transform your blanks into 0's on your Table ( I am talking about the Sales Amount column). Create a new Measure like this: Measure = 

CALCULATE(SUM(YourSalesAmountField)+0) and replace your actual Sales Amount Column with this measure. Then base your other date calculations on this measure instead of the SalesAmount Column. Does this work? you can also visit my blog for more info https://www.iipowerblog.com/ !

[Anonymous]

Hello ||PowerBlog

In my database there is no entry for the weekends. For instance on 11.06.2022 there is in the dynamic data - sales file no entry, it does not exist, because there are not sales on this date. So I cannot transform it. Take a look in the Power BI file I shared where you see the structure.

Do you think it would be useful if I would include for all firms the dates where no sales is with 0?