none
DAX moving average using a measure

    General discussion

  • I am trying to create a 12 month moving average for headcount data using the following DAX (following a blog post by Javier)

    http://javierguillen.wordpress.com/2011/09/13/calculating-moving-averages-in-powerpivot-dax/

    12 Month AVG Headcount:=
    IF(COUNTROWS(VALUES('Date'[Month Name]))=1,
    CALCULATE(AVERAGE([Ending Headcount]),
    DATESBETWEEN('Date'[Date],
    FIRSTDATE(PARALLELPERIOD('Date'[Date],-11,MONTH)),
    LASTDATE(PARALLELPERIOD('Date'[Date],0,MONTH)
    )
    ),
    ALL(DATE)
    )
    )

    The problem is that the Ending Headcount measure is a calculated measure and not a column value. Therefore I keep getting the following error message  1    "Measure 'Monthly Headcount'[12 Month AVG Headcount] : The AVERAGE function only accepts a column reference as the argument number 1"

    I don't really want to put the logic for Ending Headcount into the query above. Is there any way around this?

    btw...I did try and do a SUM(Ending Headcount) / CountRows(......) and SUM only accepts a column reference as well....

    Thanks for your help...Frank


    Frank

    Monday, March 04, 2013 4:47 PM

All replies