Rolling Average does not include zero-sales days

[karanka]

Hey there,

I am using the relationships between two tables to calculate 7-day rolling average of Acquisition Sales. Of course we don't have sales every day, so the table consists of two columns: Date and Aquisition Sales for only the dates that have acquisitions.

To consider the zero-sale days, I created a date table that includes all dates within the requested period, and the dates are linked in the relationships.

The formular I am using is:

AcqSales_RA7 = AVERAGEX(DATESBETWEEN('Date table'[Date], MAX('Date table'[Date]) - 7, MAX('Date table'[Date])), CALCULATE(SUM('Acquisition table'[Acquisition Sales])))

But the formular excludes zero-sales days.

For example, I have only 3 days with Aquisition Sales within the 7-day period of 01.10.2021 to 07.10.2021. The total sales within those three dates are 3000 Euro, So I expect the 7-day rolling average to be calculated as

3000 / 7 = 428 €

but the result on the chart is 1000 €, which means that only the 3 days with sales value have been considered (3000 / 3 = 1000), without considering the remaining 4 days without sales in that 7-day period.

Can anybody help to add something to the formular that forces it to consider zero days too? Thanks!

[amitchandak]

@karanka , Try like

AcqSales_RA7 = AVERAGEX(values('Date table'[Date]) , CALCULATE(SUM('Acquisition table'[Acquisition Sales])+0, DATESBETWEEN('Date table'[Date], MAX('Date table'[Date]) - 7, MAX('Date table'[Date]))))

[karanka]

Hey @amitchandak,

Thank you very much for your response. 

The way you wrote it gives me the sum of those 7 days (i.e. 3000 in my example). If I devide it by 7, then I get the correct result (i.e. 428). I can't understand why the AVERAGEX dosen't work there, but I could get the final result doing this:

VAR first = AVERAGEX(values('Date table'[Date]) , CALCULATE(SUM('Acquisition table'[Acquisition Sales])+0, DATESBETWEEN('Date table'[Date], MAX('Date table'[Date]) - 7, MAX('Date table'[Date]))))

return first / 7

Please let me know if you come up with the reason why your formular gives me the sum instead of average of the 7 days. Thanks!