Results 1 to 9 of 9

Thread: 3 Month Total (as of a specific date)

  1. #1
    Seeker porter444's Avatar
    Join Date
    Sep 2016
    Location
    Birmingham, AL
    Posts
    14
    Articles
    0
    Excel Version
    Excel 2016 MSO 64 Bit

    3 Month Total (as of a specific date)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    42
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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!
    Attached Files Attached Files

  3. #3
    Seeker porter444's Avatar
    Join Date
    Sep 2016
    Location
    Birmingham, AL
    Posts
    14
    Articles
    0
    Excel Version
    Excel 2016 MSO 64 Bit

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

  4. #4
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    42
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Here's a version using formulas; no PQ or PP.
    Attached Files Attached Files

  5. #5
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    42
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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
    Attached Files Attached Files

  6. #6
    Seeker porter444's Avatar
    Join Date
    Sep 2016
    Location
    Birmingham, AL
    Posts
    14
    Articles
    0
    Excel Version
    Excel 2016 MSO 64 Bit
    THANK YOU! Awesome learning opportunity!

  7. #7
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    106
    Articles
    0
    Excel Version
    Excel 365
    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).
    Attached Files Attached Files

  8. #8
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    42
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    That's remarkable, Bill. Your query must be 100, if not 200 times faster!

  9. #9
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    106
    Articles
    0
    Excel Version
    Excel 365
    Thanks, Norm :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •