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)

    12 Month AVG Headcount:=
    IF(COUNTROWS(VALUES('Date'[Month Name]))=1,
    CALCULATE(AVERAGE([Ending Headcount]),

    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


    Monday, March 04, 2013 4:47 PM

All replies