Baseline Stock

Hi Anonymous ,

The issue with your original DAX is that it's not calculating the baseline in a context-aware way across Store, EAN, and Date. It also treats the stock quantity as one big bucket instead of isolating it per combination of Store and EAN. To fix this, you need to group the data using SUMMARIZE by Date_from, Retailer_EAN, and Store, then calculate the total daily stock for each group. Once that’s done, use TOPN to extract the 30 lowest days within the past 12 months where stock is non-blank and greater than zero. Here's how you can structure the measure properly:

Baseline_Stock =
VAR CurrentDate = MAX('CalendarTable'[Date])
VAR Last12Months =
    DATESINPERIOD(
        'CalendarTable'[Date],
        CurrentDate,
        -12,
        MONTH
    )

VAR StockDataLast12Months =
    FILTER (
        COOP_Store_Inventory,
        COOP_Store_Inventory[Date_from] IN Last12Months &&
        NOT ISBLANK(COOP_Store_Inventory[Stock_quantity]) &&
        COOP_Store_Inventory[Stock_quantity] > 0
    )

VAR DailySums =
    ADDCOLUMNS (
        SUMMARIZE (
            StockDataLast12Months,
            COOP_Store_Inventory[Date_from],
            COOP_Store_Inventory[Retailer_EAN],
            COOP_Store_Inventory[Store]
        ),
        "TotalStock", CALCULATE(SUM(COOP_Store_Inventory[Stock_quantity]))
    )

VAR Lowest30 =
    TOPN (
        30,
        DailySums,
        [TotalStock],
        ASC
    )

VAR SumLowest30 =
    SUMX (Lowest30, [TotalStock])

RETURN
    DIVIDE(SumLowest30, 30)

This formula ensures that the lowest 30 days of stock per Store and EAN within the last 12 months are used in the average, giving you the proper baseline value you're looking for.

Best regards,

Hi Anonymous ,

Thanks for reaching out to the Microsoft fabric community forum.
DataNinja777 Thanks for your prompt response 
Anonymous

I've implemented the calculation logic you described using sample data, and it's working perfectly for the intended use case.

For your reference, I've also uploaded the PBIX file.

Please feel free to let us know if anything else is required — we're happy to assist further as needed.

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

Many thanks v-lgarikapat! 

But it's still not what I was looking for, if we use the dataset you create and sort on the bottom 30 "Stock_quanitiy" then the expected result for the baseline_stock on the date 10-05-2025 should be 209,93 (6.298/30) and not 13,37.

The Baseline will change for each day but not much because it would roll back one year to find the bottom 30 days.

I hope this makes it even more clear what I am looking for? 

Many thanks for the support!

Hi Anonymous ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know—we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

Many thanks for taking you time v-lgarikapat DataNinja777 ! 
I think we are very close but now I get the lowest stock pr day, location and EAN for each day.

I am a measure for just the last 30 days and it's working as wanted. so for each day I get the Average for the last 30 days divided by 30:

So I am trying to do the same but the bottom 30 days for the last 12 months and there it where with goes wrong...
Any ide on how to solve it?

Thanks for your support!