measure moving average

[PBINoob]

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]

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]

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]

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

[PBINoob]

Here's a quick image of what I'm seeing-

https://imgur.com/W657kZm

If you average the conversion rate from 4/1 to 4/10, you get 34.67% (including 4/1 to 4/10). BUT- on 4/10 the Moving Average using your formula is 35.0%. If it's using the PREVIOUS 10 days- that math doesn't work either?

ALSO- if you look at the highest spike on the bar graph- 10 days later is a pretty low dip in the line (line is the MA). 

What do you think?

[TomMartens]

Hey,

please provide a link to a PBIX with sample data

Regards

Tom

[PBINoob]

Sorry- how do I do that? Make a copy of my file, and then delete out all the tables, relationships, etc- and manually fill in the data with false data?

[PBINoob]

Here you go!

[link removed]

[TomMartens]

Hm,

following the link I got asked to login to google drive?

This will not happen!

Please make the link public, w/o the need to login to something.

Regards

Tom

[PBINoob]

Try this? It says it's a shareable link- BTW- REALLY appreciate you helping me!!!

[link removed]

[TomMartens]

Hey,

first: you are welcome

2nd: this is what I got when I follow the link

 

Unfortunately I will leave now, maybe I will have look later this evening at the latest Thursday evening

Regards

Tom

[PBINoob]

I DO believe you have to sign into google to get to it. I'll repost with it in Dropbox and see if that's different. Sorry about that!

[PBINoob]

Try this-

[link removed]

[TomMartens · Accepted solution]

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

[PBINoob]

WHOA. This is AWESOME. I never would've figured this out- THANK YOU. 

-Yes, I'm planning on scrolling my dashboard, so it's a large page 😉 
-I want 10 days, so I'll adjust it to -9 in the calcs. This was a sub-question I was going to ask later, so thank you!

QUESTION: The Avg Conversion Rate Simple and the Avgx Conversion Rate appear to be the same exact values. What's the difference here?

THANK YOU!!!

[TomMartens]

Regarding your question.about the difference of both statements, as always - it depends 😉

Sometimes the nature of the expression in this case the Division of sums does not require the somewhat more complex solution using the iterator AVERAGEX. So go with the simple one 😉

Regards
Tom

[PBINoob]

Tom-

You ROCK. THANK YOU! I learned a TON from this!

[PBINoob]

You sure that the Last Day of -10 is only really 9 days? I'm looking at the charts, and when it seems to be aliginging -10 with exactly 10 days... 

[TomMartens]

Hey

you are correct with the 10 days, i will edit my post as soon as i get back to my laptop.

Please consider to mark my post as an answer This will also help others.
Regards
Tom

[TomMartens]

Hey,

you can start with an empty PBIX and than create a table manually using 

External Data -> Enter Data from the home button or

Export the Data from a table visual check the data with Excel and make the data anonymous in Excel.

If you provide an Excel file please also provide an image of existing relationships and also provide the DAX code for existing measures (and the table the measure is associated with) and calculated columns

Regards

Tom

[PBINoob]

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!