DAX to Calculate Opening and Closing Stock, Exceed memory limit

[Analitika]

I have a large data table in Data Model from 2000 year

Why simple measure exceed memory limits? And how to get rid of that?

Opening Stock=
CALCULATE (
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] < MIN ( 'Date'[Date] ) )
)

Closing Stock=
CALCULATE (
SUM ( Table1[value] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Loading only if one month selected

[v-alq-msft]

Hi, @Analitika 

I'd like to suggest you refer to the following guidence to improve Power BI performance by optimizing DAX.

Improve Power BI Performance by Optimizing DAX 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Analitika Couple thoughts on this. First, incredibly difficult to troubleshoot performance related DAX without the model so all I can do is kind of guess. I wrote a two part series on DAX performance optimization that might help. 

• Performance Tuning DAX - Part 1 - Microsoft Power BI Community
• Performance Tuning DAX - Part 2 - Microsoft Power BI Community

Unsure if it will help, but perhaps try using a separate date table for your slicer that is not related to your main date table. You would then be able to ditch your ALL statement. Also, if you Date table is related to your fact table, you could also try filtering the table directly versus going through the Date table once you have a separate disconnected date table for your slicer. My concern with the ALL statement is that it looks like you are bringing the entirety of your fact table into context and I can't imagine that is optimal. Also, I would definitely turn off Auto date/time and the reason is that this blows up the size of your date table to huge proportions, usually unnecessarily. Then, using ALL to bring in your entire date table you have to load this behemonth into memory. Getting rid of Auto date/time should drastically reduce the size of your date table in your data model and then it may fit into memory.

[Greg_Deckler]

@Analitika - Would have to understand the data model better to understand what is going on. Do you really need the ALL in there? With the ALL in there you are overriding context forcing the measure to load the entire date table into memory and then pare it back down versus just taking what it has in context and filtering it. How big is your Date table? Have you turned off Auto date/time?

[Analitika]

@Greg_Deckler 

I have aa slicer 

it filled with auto date/time  'Date'[Date]  but sliced to last 3 years to reduce slicer dates (just need show dates for last 3 years)

but MIN(Table1[date]) is 2000.01.01

So for Opening Stock i need to sum all in Table1[value] from 2000.01.01 until MIN(Date[Date]) selected by slicer

So for Closing Stock i need to sum all in Table1[value] from 2000.01.01 until MAX(Date[Date]) selected by slicer

[amitchandak]

@parry2k , @Greg_Deckler , what is best way to this in performance efficient way?

[amitchandak]

@Analitika , See if this blog on the same topic can help

https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/

[Analitika]

inefficient DAX calculations can cause a query to try to grab a lot of memory

so how to write efficient calculation for that purpose?