AVERAGEX Function - Query

[Anonymous]

Hi,

Can someone please confirm if if the AVERAGEX function enables the evaluation of expressions for each row in the data model table or the visual table.

The reason why I'm asking is because I'm validating the average headcount across 12 months in Excel and I'm getting back 11286 but in Power BI using AVERAGEX I'm getting back 11276.

My best guess is that its evaluating expressions within the data model table that I'm calculating headcount from because if it was evaulating from the visual table it will come back with the same figure presented in Excel.

Below I've included the DAX for Headcount and Avg. Headcount.

Headcount = CALCULATE(DISTINCTCOUNT(PersonDetails[PERSON NUMBER]),
FILTER(VALUES(PersonDetails[WORK RELATIONSHIP START DATE]), PersonDetails[WORK RELATIONSHIP START DATE] <=MAX(DimDates[Date])),
FILTER(VALUES(PersonDetails[TERMINATION DATE]), OR(PersonDetails[TERMINATION DATE] >=MAX(DimDates[Date]),ISBLANK(PersonDetails[TERMINATION DATE]))))+0


Avg. Headcount = 
VAR v_dates = DATESINPERIOD(DimDates[Date], MAX(DimDates[Date]), -12, MONTH)
RETURN
AVERAGEX(v_dates, [Headcount])

[Greg_Deckler]

@Anonymous Yes, AVERAGEX is going to evaluate the measure Headcount for every row in v_dates and then take the average of that. BTW, I certainly would not trust DATESINPERIOD like, at all. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

[Anonymous]

@Greg_Deckler  Thanks for confirming, but could you please suggest what I should use instead of DATESINPERIOD in my v_dates variable?

[Greg_Deckler]

@Anonymous If it were me, I would do something like this:

Avg. Headcount = 
VAR _MaxDate = MAX( 'DimDates'[Date] )
VAR _EOM12 = EOMONTH( _MaxDate, -12 )
VAR _MinDate = DATE( YEAR( _EOM12 ), MONTH( _EOM12 ), 1 )
VAR _v_dates = SELECTCOLUMNS( FILTER( 'DimDates'[Date], [Date] <= _MaxDate && [Date] >= _MinDate ), "Date", [Date] )
RETURN
  AVERAGEX( v_dates, [Headcount] )

More verbose but you can debug it if something is amiss.