Optimize/Improve DAX Performance on AVG calculation

[rpiboy_1]

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 (2), Closed (3)

'Months Projects Open' (fact) - one row for each month a project is open

'Months Projects Open'

Project.ID	Months	Project Status
545	2025/01/01	Created
545	2025/02/01	Open
545	2025/03/01	Closed

The Months column is always the first of the month as the whole table is generated in Power Query from other data sources and we only care about granularity to the month level.

I've written a measure to return the annual average number of projects (count rows) in the row context so that I can compare the current month to the annual average. I can also slice by the Project Status so I can look at average Created & Closed.

However the measure is 'slow' relatively speaking compared to all the other measures I've written. I think my DAX is just not optimized very well, and I suspect I may not be handling my context transitions as elegantly as I could. Assuming I can improve performance I also need to do some logical handling on the 'Open' state, when it is sliced by Status, so that the Created and Closed months are included with 'Open' that is if I count only 'open' rows, I should include Created and Closed (if present).

I would love any advice on how to improve the performance of this code. Also, here is a sampe of the results in a matrix.

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 =
    COUNTROWS(
        CALCULATETABLE(
            SUMMARIZE(
                'Date',        	
                'Date'[Year Month Number]
            ),
            DATESBETWEEN('Date'[Date], _dateStart, _dateEnd)        
        )
    )
VAR _projects =
    CALCULATE(
        [Count v2],
        REMOVEFILTERS('Date'[Year Month Number]),
        _selectedYears,
        'Date'[Date] >= _dateStart,
        'Date'[Date] <= _dateEnd
    )

VAR _result =
    DIVIDE(
            _projects,
            _validMonths
        )

RETURN
    _result
    
    

[johnt75 · Accepted solution]

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.

[rpiboy_1]

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!

[bhanu_gautam]

@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