3 Month Total (as of a specific date)

[porter444]

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

 

[NormS]

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!

 

[porter444]

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).

 

[NormS]

Here's a version using formulas; no PQ or PP.

 

[NormS]

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

 

[porter444]

THANK YOU! Awesome learning opportunity!

 

[Bill Szysz]

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).

 

[NormS]

That's remarkable, Bill. Your query must be 100, if not 200 times faster!

 

[Bill Szysz]

Thanks, Norm