DAX Quarterly report expression need optimization

[RahulNadkarni]

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.

1)

                                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

2)

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

                                measure SALES_BP_Gr_Q% =

                                                                                DIVIDE(

                                                                                [SALES_FORECAST_PM_Q]

                                                                                ,

                                                                                [SALES_PY_PM_Q]

                                                                                ,0

                                                                                ) - 1

                                3)

                                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])

                                                                )

                                4)

                                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])

                                                                )

[v-saisrao-msft]

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.

[anilelmastasi · Accepted solution]

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