topic Re: slow performance of the measure xAVG_STOCK_V_DAYS in DAX Commands and Tips

slow performance of the measure xAVG_STOCK_V_DAYS

krzysztof — Wed, 09 Jul 2025 20:23:08 GMT

I have an issue with the slow performance of the measure xAVG_STOCK_V_DAYS.
In production, I have a live mode model connected to an analytical cube in Fabric. I have replicated the required tables for the calculation of the measure in the report attached here. The tables in production are dozens of times larger. The xAVG_STOCK_V_DAYS measure takes approximately 8 minutes to compute in production. I would appreciate assistance in improving the measure's performance.

https://drive.google.com/file/d/1O_jrsYubmYROx6N-_bBOJrHf4oG7oTXT/view?usp=sharing

xAVG_STOCK_V_DAYS=

xAVG_STOCK_V_DAYS = VAR DataMin = MIN ( DimDate[Date_Name] ) VAR DataMax = MAX ( DimDate[Date_Name] ) RETURN CALCULATE ( AVERAGEX ( SUMMARIZE ( 'DimDate', DimDate[Date_Name] ), [Stock] ), DATESBETWEEN ( DimDate[Date_Name], DataMin, DataMax ) )

Stock=

Stock = VAR DateChoose = CALCULATE ( MAX ( DimDate[Date_Name] ) ) + 1 RETURN CALCULATE ( SUMX ( FactInventoryStore, ( FactInventoryStore[Units] ) ), ALL ( 'DimDate' ), FactInventoryStore[TECH_DateStart] <= DateChoose, FactInventoryStore[TECH_DateEnd] > DateChoose )

Re: slow performance of the measure xAVG_STOCK_V_DAYS

v-hashadapu — Thu, 10 Jul 2025 05:09:56 GMT

Hi krzysztof , Thank you for reaching out to the Microsoft Community Forum.

The original xAVG_STOCK_V_DAYS measure was slow because it recalculated the [Stock] value for every date individually, using filters that broke context and scanned large fact tables repeatedly, a costly operation, especially on big datasets. To fix this, we rewrote the measure using ADDCOLUMNS and row context to calculate stock values more efficiently for each date in the selected range.

Please refer the attached the .pbix file and share your thoughts.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Re: slow performance of the measure xAVG_STOCK_V_DAYS

krzysztof — Thu, 10 Jul 2025 10:48:16 GMT

Unfortunately, your new measure and my measure show different results.

Re: slow performance of the measure xAVG_STOCK_V_DAYS

FBergamaschi — Thu, 10 Jul 2025 11:34:20 GMT

With this code you go back to your nrs

xAVG_STOCK_V_DAYS mia =

VAR DateMin = MIN(DimDate[Date_Name])

VAR DateMax = MAX(DimDate[Date_Name])

VAR DateChoose = DateMax+1

VAR DatesInRange =

FILTER (

ALL ( DimDate ),

DimDate[Date_Name] >= DateMin &&

DimDate[Date_Name] <= DateMax

)

VAR StockByDate =

ADDCOLUMNS (

DatesInRange,

"@Stock",

CALCULATE (

SUM ( FactInventoryStore[Units] ),

FactInventoryStore[TECH_DateStart] <= DateChoose,

FactInventoryStore[TECH_DateEnd] > DateChoose,

REMOVEFILTERS( DimDate )

)

RETURN

AVERAGEX ( StockByDate, [@Stock] )

but about performance I cannot be sure, I should check with bigger tables, can you advise if this is improving?

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: slow performance of the measure xAVG_STOCK_V_DAYS

v-hashadapu — Mon, 14 Jul 2025 09:34:20 GMT

Hi krzysztof , Thank you for reaching out to the Microsoft Community Forum.

Your original Stock measure always used MAX(Date) + 1 to calculate stock for a single future day, regardless of which date was being evaluated. So, when xAVG_STOCK_V_DAYS averaged this across a range, it was really just averaging the same number repeatedly, which explains the high and flat-looking results.

Our version took a different approach, it computed stock for each individual date in the selected range, giving a true average over time. This is logically sound and usually more insightful, but it doesn't match your original intent.

The version shared by FBergamaschi still uses the same MAX(Date) + 1 logic you had but rewrites it in a clearer and more efficient way. It produces the exact same results as your original and should perform better on larger models. If you're happy with that behaviour, this is the cleanest version to use.

Re: slow performance of the measure xAVG_STOCK_V_DAYS

krzysztof — Tue, 15 Jul 2025 16:44:38 GMT

Your measure really works quickly with large datasets. Unfortunately, it shows incorrect totals.

Re: slow performance of the measure xAVG_STOCK_V_DAYS

FBergamaschi — Tue, 15 Jul 2025 17:34:26 GMT

Aplogies, I forgot one last detail, try this

xAVG_STOCK_V_DAYS mia =

VAR DateMin = MIN(DimDate[Date_Name])

VAR DateMax = MAX(DimDate[Date_Name])

VAR DatesInRange =

FILTER (

ALL ( DimDate ),

DimDate[Date_Name] >= DateMin &&

DimDate[Date_Name] <= DateMax

)

VAR StockByDate =

ADDCOLUMNS (

DatesInRange,

"@Stock",

VAR CurrentDate = DimDate[Date_Name]

VAR DateChoose = CurrentDate + 1

RETURN

CALCULATE (

SUM ( FactInventoryStore[Units] ),

FactInventoryStore[TECH_DateStart] <= DateChoose,

FactInventoryStore[TECH_DateEnd] > DateChoose,

REMOVEFILTERS( DimDate )

)

RETURN

AVERAGEX ( StockByDate, [@Stock] )

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: slow performance of the measure xAVG_STOCK_V_DAYS

v-hashadapu — Fri, 18 Jul 2025 09:19:55 GMT

Hi krzysztof , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by FBergamaschi is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you.

Re: slow performance of the measure xAVG_STOCK_V_DAYS

v-hashadapu — Mon, 21 Jul 2025 11:18:24 GMT

Hi krzysztof , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.