The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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])
)
Solved! Go to 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. ✅
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.
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. ✅
User | Count |
---|---|
28 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |