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: ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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!