Performance issue DAX

[Antmkjr]

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

[shafiz_p · Accepted solution]

Hi @Antmkjr 

Here are some suggestions to optimize your query:

1. Using TODAY() multiple times can be inefficient. Store it in a variable and reuse it.
2. Instead of creating a calendar for the entire next year, limit it to the necessary date range.
3. 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