Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbadmin
Helper IV
Helper IV

How to calculate Moving Average based on a Rolling 10 hours

 I have a table that lists the Date, Time and SPMs. I need to calculate the MOVING Average for the SPMs column. I have tried all the different sites - but can't seem to find one that will work for what I'm trying to do. 

 

Here's some data:

'2015-12-29', '06:39:26', '33.8750'
'2015-12-29', '06:45:30', '64.8333'
'2015-12-29', '06:30:45', '116.2000'
'2015-12-29', '06:16:31', '171.0000'
'2015-12-29', '06:05:12', '276.6000'
'2015-12-29', '06:25:45', '311.1111'

 

I need to calculate the moving average of the last column This data is based off of a Rolling 10 hours. I would like to calculate the moving average as the data is coming in - as each row is added basically. So the average may start out as '33.8750' but the next row would be the average of the first row ('33.8750') and the next row ('64.8333). I know to generate a 4th calculated column but I cannot seem to get the right calculation to make it work. ANY ideas would be extremely helpful. 

I've looked on DAX sites, PowerPivot Sites, etc. 

 

Thanks in advance.

 

 

UPDATE - here's the first part of the formula I have - but I don't get any results - I'm not sure how to calculate the hours - there's plenty of examples with Dates - but I need hours:


MovingAverage = IF(COUNTROWS(VALUES('Machine2SpmRolling10Hour'[curSPM])) = 1, CALCULATE(
SUM( 'Machine2SpmRolling10Hour'[curSPM] ) / COUNTROWS( VALUES ( Machine2SpmRolling10Hour'[curSPM] ) )))

 Here's the example I pulled from: https://javierguillen.wordpress.com/2011/09/13/calculating-moving-averages-in-powerpivot-dax/ 

 

IF(COUNTROWS(VALUES(DimDate[EnglishMonthName])) = 1,
    CALCULATE(
SUM( FactResellerSales[SalesAmount] ) /  COUNTROWS(  VALUES ( DimDate[EnglishMonthName] ) )  ,
            DATESBETWEEN(
                DimDate[FullDateAlternateKey],
                FIRSTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], -2, MONTH)),
                LASTDATE(PARALLELPERIOD(DimDate[FullDateAlternateKey], 0, MONTH))
            ), ALL(DimDate)
    )
)

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

Just in case you missed this one:

 

http://www.daxpatterns.com/time-patterns/

 

Look at the Aggregation Pattern information as this should be basically what you need. Probably a little more complicated since you are working with time instead of date though. Probably will have to use EARLIER and the example from the EARLIER page might get you what you want if you modify the formula's FILTER to give you everything from the last 10 hours and use AVERAGE or AVERAGEX instead of COUNTROWS.

 

https://support.office.com/en-US/article/EARLIER-Function-DAX-90e5c2b0-50e2-417d-b4c7-7528febcef97

 

Scroll all the way to the end and read the explanation very carefully. Essentially, if your EARLIER grabbed the 3rd column value, you modified the filter to give you everything within the past 10 hours. Here is what I came up with in trying this:

 

MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))

 

Obviously, I haven't tweaked this to account for within 10 hours, on the same date, etc. but the results I came up with were:

 

MovingAverage

181.75722
162.2699
218.727775
223.8
276.6
252.9037

 

Same order as your rows. So, the earliest time value (6:05) gets 276.6, the average of its single row. The 6:16 value gets 223.8, the average of 276.6 and 171 and so on.

 

Is this what you are looking for basically?

 

One note, I imported your values into Desktop and I got rid of the single quotes and such so that the dates came in as dates, the time values came in as time values and the "value" column came in as a decimal number.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Happy to help, and for the record, EARLIER is a dumb name for that function IMHO considering how it is most often used. I would have gone with something like CURRENT.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Just in case you missed this one:

 

http://www.daxpatterns.com/time-patterns/

 

Look at the Aggregation Pattern information as this should be basically what you need. Probably a little more complicated since you are working with time instead of date though. Probably will have to use EARLIER and the example from the EARLIER page might get you what you want if you modify the formula's FILTER to give you everything from the last 10 hours and use AVERAGE or AVERAGEX instead of COUNTROWS.

 

https://support.office.com/en-US/article/EARLIER-Function-DAX-90e5c2b0-50e2-417d-b4c7-7528febcef97

 

Scroll all the way to the end and read the explanation very carefully. Essentially, if your EARLIER grabbed the 3rd column value, you modified the filter to give you everything within the past 10 hours. Here is what I came up with in trying this:

 

MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))

 

Obviously, I haven't tweaked this to account for within 10 hours, on the same date, etc. but the results I came up with were:

 

MovingAverage

181.75722
162.2699
218.727775
223.8
276.6
252.9037

 

Same order as your rows. So, the earliest time value (6:05) gets 276.6, the average of its single row. The 6:16 value gets 223.8, the average of 276.6 and 171 and so on.

 

Is this what you are looking for basically?

 

One note, I imported your values into Desktop and I got rid of the single quotes and such so that the dates came in as dates, the time values came in as time values and the "value" column came in as a decimal number.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

THANK YOU!!!! I'm not very familiar with DAX at all - so I didn't even know to look for EARLIER. It works like a charm. Much simpler than what I was trying to come up with. 🙂

 

Thank you again so much!! I've been working on this one off and on for weeks now. 

Happy to help, and for the record, EARLIER is a dumb name for that function IMHO considering how it is most often used. I would have gone with something like CURRENT.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.