Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Baseline Stock

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 ...
Need Help
  • v-lgarikapat's avatar
    v-lgarikapat
    1 year ago

    Hi Anonymous ,
    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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! 
DataNinja777's avatar
DataNinja777
Super User
1 year ago

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,