Forum Discussion

karanka's avatar
karanka
New Member
4 years ago

Rolling Average does not include zero-sales days

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!
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      karanka
      New Member
      4 years ago

      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!