DAX Quarterly report expression need optimization

Hello RahulNadkarni ,

You should minimize use of ALLSELECTED() unless really necessary. Also void multiple MAXX(...) inside visuals — extract them once as variables and keep calculation chains shallow — every nested CALCULATE, IF, or iterator adds compute time. And the last use variables aggressively to avoid recomputation.

For SALES_PY_PM_Q :

SALES_PY_PM_Q =
VAR _maxdate = MAX(fact_sales[MaxDateWithNTSData])
VAR _maxQ = QUARTER(_maxdate)
VAR _maxY = YEAR(_maxdate)
VAR _calendarMax = MAX(Calendar[Date])
RETURN
IF (
QUARTER(_calendarMax) = _maxQ && YEAR(_calendarMax) = _maxY,
CALCULATE([SALES_PY], FILTER(Calendar, Calendar[Date] <= _maxdate)),
[SALES_PY]
)

For SALES_FORECAST_PM_Q:

SALES_FORECAST_PM_Q =
VAR _maxdate = MAX(fact_sales[MaxDateWithNTSData])
VAR _maxQ = QUARTER(_maxdate)
VAR _maxY = YEAR(_maxdate)
VAR _calendarMax = MAX(Calendar[Date])
RETURN
IF (
QUARTER(_calendarMax) = _maxQ && YEAR(_calendarMax) = _maxY,
CALCULATE([SALES_FORECAST], FILTER(Calendar, Calendar[Date] <= _maxdate)),
[SALES_FORECAST]
)

For SALES_BP_Gr_Q%:

SALES_BP_Gr_Q% =
VAR _forecast = [SALES_FORECAST_PM_Q]
VAR _previous = [SALES_PY_PM_Q]
RETURN
DIVIDE(_forecast, _previous, 0) - 1

To avoid four nearly identical measures (Q1, Q2, etc.), consider one unified measure using SWITCH(Calendar[Quarter]) — useful if you can arrange quarter logic in the rows or columns.

If this solved your issue, please mark it as the accepted solution. ✅