Forum Discussion
Optimize/Improve DAX Performance on AVG calculation
You can try
Yrly Avg Proj Per Month = VAR _dateStart = STARTOFYEAR ( 'Months Projects Open'[Months] ) VAR _dateEnd = ENDOFYEAR ( 'Months Projects Open'[Months] ) VAR _selectedYears = ALLSELECTED ( 'Date'[Fiscal Year] ) VAR _validMonths = DATEDIFF ( _dateStart, _dateEnd, MONTH ) + 1 VAR _projects = CALCULATE ( [Count v2], _selectedYears, 'Date'[Date] >= _dateStart, 'Date'[Date] <= _dateEnd ) VAR _result = DIVIDE ( _projects, _validMonths ) RETURN _resultThis is using DATEDIFF rather than DATESBETWEEN, and I've removed the REMOVEFILTERS as that is automatically applied when you manipulate the filters on the date column of the date table, as long as it is marked as a date table.
You can try
Yrly Avg Proj Per Month =
VAR _dateStart =
STARTOFYEAR ( 'Months Projects Open'[Months] )
VAR _dateEnd =
ENDOFYEAR ( 'Months Projects Open'[Months] )
VAR _selectedYears =
ALLSELECTED ( 'Date'[Fiscal Year] )
VAR _validMonths =
DATEDIFF ( _dateStart, _dateEnd, MONTH ) + 1
VAR _projects =
CALCULATE (
[Count v2],
_selectedYears,
'Date'[Date] >= _dateStart,
'Date'[Date] <= _dateEnd
)
VAR _result =
DIVIDE ( _projects, _validMonths )
RETURN
_result
This is using DATEDIFF rather than DATESBETWEEN, and I've removed the REMOVEFILTERS as that is automatically applied when you manipulate the filters on the date column of the date table, as long as it is marked as a date table.
This version is at least 'cleaner', based on observation it seems like it may be nominally more performant, but is still 'slow' relativley speaking. It may be that there is nothing to be done to improve performance given the number of rows that have to be iterated in the context(s) to get the appropriate answer. If anyone else has suggestions that would be great!