Baseline Stock

[Stoppelaar]

Greetings,

I have tested and tested and asked Co-pilot and everything but I cant get this to work and I am getting mad...

So I am trying to make a baseline on stock quantity and the logic is this following.

Over the last 12 month, find the 30 days with the bottom/lowest stock quantity (ignorr Blank/Null),

SUM the 30 bottom/lowest days and then Divide it by 30 to make the baseline.

My data table includes: Stores, EAN, Date_From and Stock_quantity

The DAX Co-pilot wants me to use is as below but it only gives me the same result as SUM Stock_quantity...

Sum_Lowest_30_Days_Stock_Last_Year =

VAR CurrentDate = MAX(CalendarTable[Date])  // Get the current context date

VAR LastYear =

    DATESINPERIOD(

        CalendarTable[Date],

        CurrentDate,

        -12,

        MONTH

    )

VAR DailyStockSum =

    SUMMARIZE(

        FILTER(

            COOP_Store_Inventory,

            COOP_Store_Inventory[Date_from] IN LastYear &&

            COOP_Store_Inventory[Stock_quantity] > 0 // Filter out zero or blank stock

        ),

        COOP_Store_Inventory[Date_from],  // Group by Date_from

        COOP_Store_Inventory[Retailer_EAN], // Include Retailer_EAN for context

        "DailyTotal", SUM(COOP_Store_Inventory[Stock_quantity]) // Create DailyTotal

    )

VAR Smallest30Days =

    TOPN(

        30,

        DailyStockSum,

        [DailyTotal],

        ASC

    )

VAR TotalStockBottom30Days =

    SUMX(Smallest30Days, [DailyTotal]) // Sum the DailyTotal from the smallest 30 days

RETURN

    TotalStockBottom30Days

 Any idea on what I can do or test to make this work? 

I will use it on both Store, EAN and date level to get the baseline stock level.

Many thanks.

[v-lgarikapat · Accepted solution]

Hi @Stoppelaar ,
Thanks for reaching out to the Microsoft fabric community forum.
I have modified the Logic for  bottom 30 "Stock_quanitiy
Here is the updated  DAX 

Baseline_Stock_Last12M_Bottom30_v3 =

VAR CurrentDate = SELECTEDVALUE('CalendarTable'[Date])

-- Define 12-month window

VAR Last12Months =

    DATESINPERIOD(

        'CalendarTable'[Date],

        CurrentDate,

        -12,

        MONTH

    )

-- Filter data in the 12-month period where stock > 0

VAR StockData =

    FILTER (

        COOP_Store_Inventory,

        COOP_Store_Inventory[Date_from] IN Last12Months &&

        COOP_Store_Inventory[Stock_quantity] > 0

    )

-- Group by Date, Store, EAN → calculate total stock per day-location-product

VAR DailySums =

    ADDCOLUMNS (

        SUMMARIZE (

            StockData,

            COOP_Store_Inventory[Date_from],

            COOP_Store_Inventory[Store],

            COOP_Store_Inventory[Retailer_EAN]

        ),

        "TotalStock", CALCULATE(SUM(COOP_Store_Inventory[Stock_quantity]))

    )

-- Take 30 days with lowest stock

VAR Bottom30 =

    TOPN (

        30,

        DailySums,

        [TotalStock],

        ASC

    )

-- Average the lowest 30 daily totals

VAR AvgBaseline =

    AVERAGEX(Bottom30, [TotalStock])

RETURN

    AvgBaseline
Note: Visual-level filters are not required

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.

[Stoppelaar · Accepted solution]

Hi @v-lgarikapat & @DataNinja777, sorry for not geeting back to you! some other project came up but now I got some time and I think I solved it, not 100% but very close.

I had to make a rank on Stock Qty on all dates:

CurrentRank =

VAR CurrentValue = [Stock Qty]

RETURN

    IF(

        ISBLANK(CurrentValue) || CurrentValue = 0,

        BLANK(),  -- Return blank if the current value is blank or zero

        RANKX(

            FILTER(

                ALL(CalendarTable[Dato]),  -- Rank based on all unique Dates

                NOT(ISBLANK([Stock Qty])) && [Stock Qty] > 0  -- Exclude blank and zero values

            ),

            [Stock Qty],  -- Use your existing "Stock" measure for the ranking

            ,  -- No value for ties, defaults to the next rank

            ASC,  -- Ranking in ascending order (lowest stock gets rank 1)

            DENSE  -- Use DENSE to avoid gaps in ranking

        )

    )

Then I did the rank and date to get the 30 bottom days and last step was to make an average:

Baseline Stock =

VAR Bottom30Days =

    TOPN(

        30,  -- Get the bottom 30 days

        FILTER(

            ALL(CalendarTable),  -- Consider all dates

            NOT(ISBLANK([Stock Qty])) && [Stock Qty] > 0  -- Exclude blanks and zeros

        ),

        [CurrentRank],  -- Order by CurrentRank

        ASC  -- Get the lowest ranks

    )

RETURN

    AVERAGEX(

        Bottom30Days,  -- Iterate over the bottom 30 days

        [Stock Qty]  -- Calculate the average of Total_Stock_Per_Day

    )

It now gives me one value and I will use this as the baseline for the stocks.

Thanks for your support! 

[Stoppelaar]

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!

[v-lgarikapat · Accepted solution]

Hi @Stoppelaar ,
Thanks for reaching out to the Microsoft fabric community forum.
I have modified the Logic for  bottom 30 "Stock_quanitiy
Here is the updated  DAX 

Baseline_Stock_Last12M_Bottom30_v3 =

VAR CurrentDate = SELECTEDVALUE('CalendarTable'[Date])

-- Define 12-month window

VAR Last12Months =

    DATESINPERIOD(

        'CalendarTable'[Date],

        CurrentDate,

        -12,

        MONTH

    )

-- Filter data in the 12-month period where stock > 0

VAR StockData =

    FILTER (

        COOP_Store_Inventory,

        COOP_Store_Inventory[Date_from] IN Last12Months &&

        COOP_Store_Inventory[Stock_quantity] > 0

    )

-- Group by Date, Store, EAN → calculate total stock per day-location-product

VAR DailySums =

    ADDCOLUMNS (

        SUMMARIZE (

            StockData,

            COOP_Store_Inventory[Date_from],

            COOP_Store_Inventory[Store],

            COOP_Store_Inventory[Retailer_EAN]

        ),

        "TotalStock", CALCULATE(SUM(COOP_Store_Inventory[Stock_quantity]))

    )

-- Take 30 days with lowest stock

VAR Bottom30 =

    TOPN (

        30,

        DailySums,

        [TotalStock],

        ASC

    )

-- Average the lowest 30 daily totals

VAR AvgBaseline =

    AVERAGEX(Bottom30, [TotalStock])

RETURN

    AvgBaseline
Note: Visual-level filters are not required

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.

[v-lgarikapat]

Hi @Stoppelaar ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @Stoppelaar ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, we kindly request you to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If our response was helpful, please mark it as Accepted as Solution and consider giving a Kudos. Feel free to reach out if you need any further assistance.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @Stoppelaar ,

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

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.

[Stoppelaar]

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:

Average_Stock_Last_30_Days =

VAR TotalStockLast30Days =

    CALCULATE(

        SUM(COOP_Store_Inventory[Stock_quantity]),

        DATESINPERIOD(CalendarTable[Date], MAX(CalendarTable[Date]), -30, DAY)

    )

RETURN

    DIVIDE(TotalStockLast30Days, 30, 0)

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!

[v-lgarikapat]

Hi @Stoppelaar ,

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.

[DataNinja777]

Hi @Stoppelaar ,

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,