# DAX Calculation based on Previous Day and a Constant value

Hi Team,

I am trying to do DAX calculation based on below logic…

Storage/Day = #Total Storage Location from Previous Day + Count of Pallet-LPN - 116.

As I need to subtract a constant number it is challenging somehow I calculated and got the correct number for Date 16-Feb to 20-Feb but it is very slow in production. It takes around 1 mint for 2 months of data and my data keeps on growing …

I am attaching the sample PBIX file and below is the DAX I used to get the number. My actual data starts from 31-Dec-2020 which is just used to get the data from 1-Jan-2021 and it will be hidden from user.

``````Storage/Day =
VAR currDate = SELECTEDVALUE('Date'[Date])
VAR prevDay =
IF (
MONTH ( SELECTEDVALUE ( 'Date'[Date] ) ) < MONTH ( currDate ),
IF ( NOT ( ISINSCOPE ( 'Item'[Profit Center] ) ), 9000 ),
CALCULATE ( [# Total Location_Storage], PREVIOUSDAY ( 'Date'[Date] ) )
)
RETURN
IF (
NOT ( ISBLANK ( [# Total Location_Storage] ) ),
IF (
ISINSCOPE ( 'Item'[Profit Center] ),
prevDay + [Sum of Pallet-LPN],
prevDay + [Sum of Pallet-LPN] - 116
)
)
``````

9000 - has been used to get the correct number for 31-Dec-2020 .

@MK3010 Replace DISTINCTCOUNT with SUMX ( DISTINCT ( ‘Storage’[Location] ), 1 ), now your visual runs in ~140 ms

compared to DISTINCTCOUNT:

The problem is that DISTINCTCOUNT is a non-additive function so for each change in filter context FE ask SE for a separate data cache, and even if each request is executed in 1ms then 1ms * 1475 would be just for DISTINCT Values, with SUMX FE simply ask everything at once and then does DISTINCTCOUNT

5 Likes

COUNTROWS ( SUMMARIZE( ‘Storage’, ‘Storage’[Location] ) ) after reading it in another post but this didn’t improve performance much but now I understood.

And on the Storage/Day measure how do you deal with a constant value addition/subtraction. Here I tried checking for the total and then subtract the constant value…

Thanks
Mukesh

1 Like

@MK3010 Constants won’t impact the performance at all.

I meant, the way I have subtracted the constant from total is correct, mostly you always have better and optimized way of DAX which gives new way to write for future DAX…

Thanks
Mukesh

@MK3010 All set, the only thing I would suggest is when you have to use the same measure in different branches of IF then store it in a variable outside IF, if a measure is only used once inside IF then always declare it inside IF

Here is an example:

With variables - 2 Storage Engine Queries:

Without variables - 3 Storage Engine Queries:

1 Like