DAX Quarterly report expression need optimization

RahulNadkarni — Wed, 30 Apr 2025 08:43:49 GMT

Hi everyone,

I have one DAX KPI that is creating performance issue. It's giving correct result 🙂 but it will need to be optimized.

Request you to provide insight.

It's created for Quarterly report where year will be added in column in MAtrix Table.

In measures , 4 measures for each quarter are added.

Find the expression for Q1. Similar filter added for Q2,Q3,Q4.

This filter the measure with current quarter

measure SALES_BP_Q1_Gr% =

var _value = CALCULATE([SALES_BP_Gr_Q%],filter(Calendar,Calendar[Quarter] = "Q1"))

return

_value

Utilization% = forecated sale current year / actaul sale if previous year

measure SALES_BP_Gr_Q% =

DIVIDE(

[SALES_FORECAST_PM_Q]

[SALES_PY_PM_Q]

) - 1

Below will calculate the previous year sales till the current month of sales.

If current data is for month is April then Quarterly report should compare with previous year april month

only and not entire quarter 2. Hence I am using the MaxDateWithNTSData Field

measure SALES_PY_PM_Q =

var _maxdate = MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData])

var _maxQ = Quarter(MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData]))

var _maxY = Year(MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData]))

return

IF(QUARTER(max(Calendar[Date])) = _maxQ && Year(max(Calendar[Date])) = _maxY,

CALCULATE([SALES_PY]

,FILTER(Calendar,Calendar[Date] <= _maxdate )

)

CALCULATE([SALES_PY])

)

Below will calculate the forecasted sales till the current month of sales.

If current data is for month is April then Quarterly report should compare with previous yar april month

only and not entire quarter 2

measure SALES_FORECAST_PM_Q =

var _maxdate = MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData])

var _maxQ = Quarter(MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData]))

var _maxY = Year(MAXX(ALLSELECTED(fact_sales), fact_sales[MaxDateWithNTSData]))

return

IF(QUARTER(max(Calendar[Date])) = _maxQ && Year(max(Calendar[Date])) = _maxY,

CALCULATE([SALES_FORECAST]

,FILTER(Calendar,Calendar[Date] <= _maxdate )

)

CALCULATE([SALES_FORECAST])

)

Re: DAX Quarterly report expression need optimization

anilelmastasi — Wed, 30 Apr 2025 13:35:45 GMT

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. ✅

Re: DAX Quarterly report expression need optimization

v-saisrao-msft — Fri, 02 May 2025 07:10:54 GMT

Hi RahulNadkarni,
I wanted to check if you had the opportunity to review the information provided by anilelmastasi . Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

topic Re: DAX Quarterly report expression need optimization in DAX Commands and Tips

DAX Quarterly report expression need optimization

Re: DAX Quarterly report expression need optimization

Re: DAX Quarterly report expression need optimization