+ Reply to Thread
Results 1 to 2 of 2

Powerpivot DAX forrmula 12month average incorrect

  1. #1
    Registered User
    Join Date
    07-20-2004
    Posts
    40

    Powerpivot DAX forrmula 12month average incorrect

    I have this powerpivot table :
    Fiscal Revenue_Value
    Jan-12 761238
    Feb-12 538234
    Mar-12 503990
    Apr-12 578607
    May-12 794209
    Jun-12 857749
    Jul-12 683114
    Aug-12 765772
    Sep-12 638268
    Oct-12 664654
    Nov-12 677114
    Dec-12 481902
    Jan-13 717801
    Feb-13 554196
    Mar-13 467578
    Apr-13 861125
    May-13 547982
    Jun-13 843557
    Jul-13 804521
    Aug-13 633815
    Sep-13 903311
    Oct-13 866296

    I am trying to use a correct DAX formula of the 12 month average, but the formula is incorrect. This is what I need help with.
    Also, my data starts at Jan 2012. So in the Mar 2012 line I would like to average Jan,Feb,Mar 2012 even though I don't have 12 months worth of data.
    This DAX formula calculates correctly until I get the 13th month. I underlined what is correct and italicized what is incorrect.

    DAX formula :
    =calculate(CALCULATE (
    SALES[Revenue_Value],
    DATESBETWEEN (
    SALES[Fiscal],
    NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE (SALES[Fiscal] ) ) ),
    LASTDATE (SALES[Fiscal])
    )
    )/(CALCULATE (
    CALCULATE(COUNTROWS(VALUES(SALES[FY_Mnth])), Sales ),
    DATESBETWEEN (
    SALES[Fiscal],
    SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE (SALES[Fiscal]))),
    LASTDATE (SALES[Fiscal])
    )
    )))

    Here is what I have now :
    Fiscal Revenue_Value 12 mos av
    Jan-12 761238 761238
    Feb-12 538234 649736
    Mar-12 503990 601154
    Apr-12 578607 595517
    May-12 794209 635256
    Jun-12 857749 672338
    Jul-12 683114 673877
    Aug-12 765772 685364
    Sep-12 638268 680131
    Oct-12 664654 678584
    Nov-12 677114 678450
    Dec-12 481902 662071
    Jan-13 717801 721888
    Feb-13 554196 768071
    Mar-13 467578 807036
    Apr-13 861125 878796
    May-13 547982 924461
    Jun-13 843557 994758
    Jul-13 804521 1061801
    Aug-13 633815 1114619
    Sep-13 903311 1189895
    Oct-13 866296 1262086

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,817

    Re: Powerpivot DAX forrmula 12month average incorrect

    If you are still looking for an answer, this article on Moving Averages may help.

+ 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. PowerPivot - Average of the values in a row
    By shadedrivein in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-14-2014, 06:16 PM
  2. [SOLVED] Powerpivot Rolling Average Calculated Measure
    By hbusche in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-15-2013, 06:46 PM
  3. The function AVERAGE gives incorrect output
    By RubySudalaimani in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 05:22 AM
  4. making a sheet with a 12month running total
    By and01 in forum Excel General
    Replies: 10
    Last Post: 04-26-2012, 01:04 PM
  5. IF forrmula
    By solarris in forum Excel General
    Replies: 5
    Last Post: 09-03-2011, 10:30 PM
  6. question about 12month rolling data
    By tink_ in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2007, 01:51 AM
  7. Forrmula Formatting question...
    By Tibbets in forum Excel General
    Replies: 2
    Last Post: 04-21-2006, 06:20 PM

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