Forum Discussion

rpiboy_1's avatar
rpiboy_1
Helper V
1 year ago
Solved

Optimize/Improve DAX Performance on AVG calculation

I have a data model with the following tables:   'Date' (well formed) 'Projects' (dimension) - a list of all projects 'Project Status' (dimension) - status a project can have, Created (1), Opened...
optimization
  • johnt75's avatar
    johnt75
    1 year ago

    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.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

rpiboy_1 , Try using

DAX
Yrly Avg Proj Per Month =
VAR _dateStart = STARTOFYEAR('Months Projects Open'[Months])
VAR _dateEnd = ENDOFYEAR('Months Projects Open'[Months])
VAR _validMonths = COUNTROWS(DATESBETWEEN('Date'[Date], _dateStart, _dateEnd))

VAR _projects =
CALCULATE(
[Count v2],
REMOVEFILTERS('Date'[Year Month Number]),
DATESBETWEEN('Date'[Date], _dateStart, _dateEnd),
'Project Status'[Status] IN {"Created", "Open", "Closed"}
)

VAR _result = DIVIDE(_projects, _validMonths)

RETURN _result