Moving 16 week Average

Hi all,

Let me describe what help I am looking for.

I want to create a Driver Performance dashboard around the data I have in the attachment.

So, I want to setup a page with a combo box that I would select a driver name, this in turn would display details of this driver on this page. One thing I want to do is compare the selected driver to other drivers on the same shift and compare average hours per Work order which is the WK Avg column.

I want to compare only the last 16 weeks of the data, so I am looking at how to create a "moving average" on my data as I add data every week.

I then want to chart all the drivers on the same shift and compare to the driver selected from the combo box for the past 16 weeks.

I hope this makes sense!

You're already calculating an average of an average, in your Wk Avg column. Presumably you are intending to average the weekly averages, in your "Moving 16 Week Average" measure? This won't give you truly accurate results.

You'd be better recording true source data - so each row in your data would record the Driver Name, Date, Shift, Location, Route, # Work Orders and Hours Worked. It would make more sense to have one row per day / shift, rather than one row per week.

With the data shaped like that, and a 'Date' table added, you could easily use Power Pivot to create a "Moving 16 Week Average" measure, using DAX:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

With these measures in your Data Model, it's fairly easy to layout your report as you wish.

See attached file for a worked example (I've calculated notional Hours / Work Orders from your data, to demonstrate the concept)

Hi Olly,

Ok, thanks for your reply and thoughts and your example. I will digest what you have outlined and get back to you with any questions.

Tim201110,
I appreciate your reply as well, I will review your formula later today!
Thanks

Hi Tim201110,

I have had time to review your formula and it does give me a 16 week average as requested.
So, now my next question is how can the formula then give me a weekly total of each shift within the 16 week average for charting each shift?
Please see my attachment for my desired output.

Thanks