Forum Discussion

PBINoob's avatar
PBINoob
Helper I
8 years ago
Solved

measure moving average

I've searched and tried a number of things- can't seem to get this to work...

I have a Measure:
Conversion Rate =
DIVIDE((SUM(OrderItems[Quantity])), (SUM(Sessions[Sessions])))

I'd like a 10 Day Moving Average of that Measure.

 

I'm using a Calendar Table for the time. I have something like this, but can't seem to figure this out. 
DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]),-10,DAY)

 

I'd really appreciate any direction here!

Thanks!

  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey, 

     

    here you will find my pbix file. I just added another page and created a simple table, this way was somewhat easier to read, than deduct the expected result from your graphs (wondering about the size of your screen :-) ).

     

    I created 4 measure, two measures calculate the sum of quantities and sessions for the last 10days. The measure for quantity looks like this (session is similar)

     

    sum quantity last 10 = 
var mylastdate = LASTDATE(VALUES(Calendar[Date]))
var sumQuantity =
CALCULATE(
    SUM('OrderItems'[Quantity])
    ,DATESINPERIOD(Calendar[Date], mylastdate,-10,DAY)
)
return
sumQuantity

    And two measure that calculate the average

    • the first one "avg conversion rate simple" dividing by dividing [sum quantity last 10] / [sum sessions last 10]
    • the seond one "avgx conversion rate" iterates over the last 10 days and calculates the average from the divisions for each day

    Here is the DAX of the first one, I recreated measures as variables to be sure to understand what's happening, I guess you can replace some of the DAX statement by simply using your existing measures:

    avg conversion rate simple = 
var mylastdate = LASTDATE(VALUES(Calendar[Date]))
var sumQuantity =
CALCULATE(
    SUM('OrderItems'[Quantity])
    ,DATESINPERIOD(Calendar[Date], mylastdate,-10,DAY)
)
var sumSessions =  
CALCULATE(
    SUM('Sessions'[Sessions])
    ,DATESINPERIOD(Calendar[Date], mylastdate,-10,DAY)
)
return
DIVIDE(sumQuantity, sumSessions, BLANK())

     

    Here is a screenshot of the table i used to validate the calculations:

     

     

    One comment:

    Wrong: Using LastDate(...) -10 basically leads to a timeframe that contains 11 days, the lastdate (date of the current row) and 10 days before ;-)

     

    Hopefully this is what you are looking for

     

    Regards

    Tom

     

     

     

     

21 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    maybe you can give this a try:

    Last Ten Days = 
var theLastDate = LASTDATE('Calendar'[Date])
return
CALCULATE(
[Conversion Rate]
,ALL('Calendar'[Date]) // maybe ALL('Calendar')
,DATESINPERIOD('Calendar'[Date], theLastDate,-10,DAY)
)

    Hopefully this is what you are looking for

     

    Regards

    Tom

     

     

    • PBINoob's avatar
      PBINoob
      Helper I
      8 years ago

      Thanks for the reply.
      It's not quite right. When I look at the visual, the Moving Avg doesn't line up with the underlying data. ie- a big spike in Conversion Rate doesn't reflect a big spike in MA 10 days later.

      ALSO- when I do the math manually (ie- average the Conversion Rate over a 10 day period), the MA number doesn't match the number I am getting.

      Your solution SEEMS like it would work, but, not when I apply it. Any ideas?

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        sorry to hear, that it looks good but behaves not good enough :-)

         

        Maybe you might to consider to provide a pbix with sample data, upload the file to onedrive or dropbox and share the link

         

        Regards

        Tom