+ Reply to Thread
Results 1 to 5 of 5

12 months moving average measure

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    Question 12 months moving average measure

    With my limited knowledge of PopwerPivot I'm trying to create two measures that can calculate a 12 months moving average of my hitrate.

    I have succeeded in creating two measures that calculate 'hitrate of the month' based on both SUM and COUNT. My definition of hitrate in this case is: Awarded / (Awarded + Lost) = Hitrate.

    The two measures looks like this:

    SUM:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    COUNT:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Capture.PNG

    Now I'm trying to create two measures that calculate a 12 months moving average for both SUM and COUNT. However, this time around I have no idea where to start.

    I'm using a fairly simple sample file for this purpose. It only contain one table and four columns. The file can be downloaded here:

    Sample2.xlsx

    I hope you can help me or give me a push in the right direction.

    Best regards,
    Nielf
    Last edited by nielf; 09-23-2015 at 03:20 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,802

    Re: 12 months moving average measure

    Hi
    external links being potentially unsafe and unreachable for some members, please post your file on the forum - Thx

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: 12 months moving average measure

    I know PT's are extremely powerful, but just sometimes it might be better to use formulas. I think maybe this may be 1 of those times.

    I created a small table, with consecutive dates, and then used SUMIFS and COUNTIFS, with EDATE() built in...
    E
    F
    G
    3
    1/1/2015
    51.09%
    57.14%
    4
    2/1/2015
    16.91%
    20.00%
    5
    3/1/2015
    51.19%
    40.00%
    6
    4/1/2015
    50.03%
    50.00%
    7
    5/1/2015
    56.55%
    60.00%
    8
    6/1/2015
    42.48%
    33.33%
    9
    7/1/2015
    77.74%
    66.67%
    10
    8/1/2015
    29.28%
    40.00%
    11
    9/1/2015
    36.75%
    50.00%

    F3=SUMIFS(Data!$B$2:$B$114,Data!$D$2:$D$114,"awarded",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1))/(SUMIFS(Data!$B$2:$B$114,Data!$D$2:$D$114,"lost",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1))+SUMIFS(Data!$B$2:$B$114,Data!$D$2:$D$114,"awarded",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1)))

    G3=COUNTIFS(Data!$D$2:$D$114,"awarded",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1))/(COUNTIFS(Data!$D$2:$D$114,"lost",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1))+COUNTIFS(Data!$D$2:$D$114,"awarded",Data!$C$2:$C$114,">="&Hitrate!E3,Data!$C$2:$C$114,"<"&EDATE(E3,1)))
    Both copied down

    For the 12-month average, change all the EDATE() from ,1 to ,12
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-01-2015
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: 12 months moving average measure

    Hi FDibbins,

    Thanks for your reply. One of the reasons I'm using PivotTables is that I need to be able to slice the data. Can I do that with the solution you have come up with? I don't have Excel on the computer I'm replying from so I haven't been able to test it yet.

  5. #5
    Registered User
    Join Date
    07-01-2015
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: 12 months moving average measure

    I found a solution.

    I had to create a Calendar Table and then I used the logic described here:

    http://www.powerpivotpro.com/2013/07...ages-sums-etc/

    You can see the result here:

    Hitrate.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dax Formula - 3 Months & 6 Months Average
    By Macondo73 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-20-2015, 03:44 PM
  2. Replies: 1
    Last Post: 10-20-2014, 03:20 PM
  3. [SOLVED] Powerpivot Rolling Average Calculated Measure
    By hbusche in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-15-2013, 06:46 PM
  4. Replies: 4
    Last Post: 05-23-2012, 03:22 PM
  5. [SOLVED] Average - 3 months / 6 months trend line ( array formula? )
    By ccernat in forum Excel General
    Replies: 3
    Last Post: 04-04-2012, 06:24 AM
  6. Average Months into Years/Months
    By mv835 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2011, 02:45 AM
  7. Charts 5 day moving average, 10 day moving average
    By monalisa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2008, 09:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1