Forum Discussion

Milejdi8's avatar
Milejdi8
Frequent Visitor
3 years ago

Moving average with conditions

Hello, 
I have a Query with 4 columns: customer, day, product, amount sold.
I would like to have, in this query or another, for each customer / day / product calculation of the average sales of previous x days and next x days. 
Ie. for customerA, product12, for March 13th, I'd like to see average of sales of this product in the period March 3-23th  (+/-10 days in this case). 

Table is uploaded daily so it's not sorted per customer/product, and I'd like to skip this step as in larger tables it can be time consuming. 


This info can be either in the same Query as a separate column, or I could make a separate query with some calculations (from some reason I thought it'd be easier but it doesn't seem so)...
I've spent crazy time searching for solution but couldn't find any...
Any help appreciated! 

Need Help

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

    Amount total: = 
SUM( Sales[Amount Sold] )

     

    Expected result measure: =
VAR _currentrowdate =
    MAX ( 'Calendar'[Date] )
VAR _previousxdays = 10
VAR _nextydays = 10
VAR _daterange =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] >= _currentrowdate - _previousxdays
            && 'Calendar'[Date] <= _currentrowdate + _nextydays
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Date] ),
        AVERAGEX ( _daterange, [Amount total:] )
    )
    • Milejdi8's avatar
      Milejdi8
      Frequent Visitor
      3 years ago

      Hello, 
      You've pretty much got the model right - I didn't include it as it has additional tables as well but for the one I'm working on, that's it! 
      Your solution seems very near to what I'd like to achieve, the only question is - does it take into account Customer/product? I can't see part of the code that'd refer to that part, which might as well be due to my still beginners knowledge.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your message.

        If you create Customer slicer and Product slicer on the same page, it will influcence the visualization as well.

        Thank you.