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! 
Anonymous's avatar
Anonymous
Not applicable
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!