Forum Discussion
AVERAGEX Function - Query
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'...
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
Greg_Deckler Thanks for confirming, but could you please suggest what I should use instead of DATESINPERIOD in my v_dates variable?
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.