Performance issue DAX

Anonymous — Wed, 27 Nov 2024 08:39:12 GMT

Is there a way to optimize the performance of below query

Sale from Current Inventory Balance NY =
VAR CurrentDate =
TODAY ()
VAR InitialValue = [Opening Bal]
VAR CurrentYear =
YEAR ( CurrentDate )
RETURN
SUMX (
FILTER (
ADDCOLUMNS (
CALENDAR ( TODAY (), DATE ( YEAR ( TODAY () ) + 1, 12, 31 ) ),
"DaysSinceStart", DATEDIFF ( CurrentDate, [Date], DAY ),
"DailyValueSub",
IF (
DATEDIFF ( CurrentDate, [Date], DAY ) < 'Day Range'[Day Range Value],
DIVIDE ( [Opening Bal], 'Day Range'[Day Range Value] )
)
),
[Date] >= CurrentDate
&& YEAR ( [Date] )
= YEAR ( TODAY () ) + 1
),
[DailyValueSub]
)

Re: Performance issue DAX

shafiz_p — Wed, 27 Nov 2024 09:13:38 GMT

Hi Anonymous

Here are some suggestions to optimize your query:

Using TODAY() multiple times can be inefficient. Store it in a variable and reuse it.
Instead of creating a calendar for the entire next year, limit it to the necessary date range.
Calculate DaysSinceStart only once and reuse it.

Here’s an optimized version (1) of your query, you could try this:

SaleFromCurrentInventoryBalanceNY = VAR CurrentDate = TODAY() VAR InitialValue = [Opening Bal] VAR CurrentYear = YEAR(CurrentDate) VAR EndDate = DATE(CurrentYear + 1, 12, 31) VAR DateRange = CALENDAR(CurrentDate, EndDate) RETURN SUMX( FILTER( ADDCOLUMNS( DateRange, "DaysSinceStart", DATEDIFF(CurrentDate, [Date], DAY), "DailyValueSub", IF( DATEDIFF(CurrentDate, [Date], DAY) < 'Day Range'[Day Range Value], DIVIDE(InitialValue, 'Day Range'[Day Range Value]) ) ), [Date] >= CurrentDate && YEAR([Date]) = CurrentYear + 1 ), [DailyValueSub] )

Version 2:

SaleFromCurrentInventoryBalanceNY = VAR CurrentDate = TODAY() VAR InitialValue = [Opening Bal] VAR CurrentYear = YEAR(CurrentDate) VAR EndDate = DATE(CurrentYear + 1, 12, 31) VAR DateRange = CALENDAR(CurrentDate, EndDate) VAR FilteredDates = FILTER( DateRange, [Date] >= CurrentDate && YEAR([Date]) = CurrentYear + 1 ) VAR CalculatedValues = ADDCOLUMNS( FilteredDates, "DaysSinceStart", DATEDIFF(CurrentDate, [Date], DAY), "DailyValueSub", IF( DATEDIFF(CurrentDate, [Date], DAY) < 'Day Range'[Day Range Value], DIVIDE(InitialValue, 'Day Range'[Day Range Value]) ) ) RETURN SUMX( CalculatedValues, [DailyValueSub] )

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

topic Re: Performance issue DAX in DAX Commands and Tips

Performance issue DAX

Re: Performance issue DAX