3 Month Total (as of a specific date)

porter444

porter444

New member
Joined
Sep 8, 2016
Messages
14
Reaction score
0
Points
0
Location
Birmingham, AL
Excel Version(s)
Excel 2016 MSO 64 Bit
I've got a set of data where I need to get an average weekly performance measurement (over the last 3 months). We'll use the results to understand staffing needs.

Here's the basic information contained in the sample data file (attached):
  • Date Update: date the transactions took place (daily activity)
  • Branch, Region & District: geography of the business units
  • Type: just let's me know if the branch is a hub or a branch
  • System Lines - the number of lines (Ticket Pulling & Receiving) that were transacted in that branch on that day

What I need to end up with: For each "Date Updated", "Branch" combination, the total "System Lines" (for ticket pulling and receiving separately) for the prior 3 months.

I can do the remaining calculations to get to the average weekly no problem after that (3 month value divided by 13 weeks).

Here is a video that gives an some more info --> https://youtu.be/39t5FqI3aJs
 

Attachments

  • sample data.xlsx
    288.8 KB · Views: 16
You can load your existing table into Power Pivot, create measures to calculate the weekly average over the past 3 months, then use the measures in a pivot table. See the attached, had to delete some of the data in order to upload it.

Norm

PS. It just dawned on me that your version of Excel may not have Power Pivot, and could be the reason you're asking how to do this in Power Query!
 

Attachments

  • sample data PP.xlsx
    283.7 KB · Views: 10
Norm, thanks for the reply. I do have PQ & PP loaded. This definitely does work, but still would like to see a PQ solution if it's possible (for the sake of options).
 
Here's a version using formulas; no PQ or PP.
 

Attachments

  • sample data XL.xlsx
    62.4 KB · Views: 7
Rounding it out, here's a Power Query version. I created a function to perform the equivalent of the SUMPRODUCT functions in my XL workbook. However, I imagine someone can do this much more efficiently since with your full data set it takes more than 7 minutes to refresh on my machine!

Norm
 

Attachments

  • sample data PQ.xlsx
    162.7 KB · Views: 6
Hi porter444 :)
For clarity... this is the job for PP...but we can do this also in PQ however if you choose wrong way then speed/performance will be dramaticaly low.
This is only first 100 rows becouse of forum rules. (Delete the last step in the query to get the full table).
 

Attachments

  • sample_data_BS_PQ .xlsx
    323.3 KB · Views: 29
That's remarkable, Bill. Your query must be 100, if not 200 times faster!
 
You must log in or register to reply here.
Back
Top