slow performance of the measure xAVG_STOCK_V_DAYS

[krzysztof]

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
    )

[v-hashadapu]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[krzysztof]

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

[v-hashadapu]

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.

[FBergamaschi]

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

[krzysztof]

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

[FBergamaschi · Accepted solution]

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