Solved: measure moving average - Page 2

PBINoob · ‎05-22-2018

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 · ‎05-23-2018

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Greg_Deckler · ‎05-22-2018

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Also, check out my Rolling Weeks. should be able to modify for Rolling Days.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

measure moving average

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly