Inventory Level for every day, also dates with no row entry

[Wonter]

Hi all,

I have a question about the inventory levels. I've tried so many different options, but nothing worked yet. I'm looking for a way to visualise every day in a table with for every day the lowest inventory level of that specific day. There are a couple of challenges. 

- Multiple products in multiple stores

- When a change in the inventory level is made, I have a new row in the 'historicalinventory' with history_id, history_date (timestamp with date and time), Date (only the date), store_id, product_id and the stock (new level of inventory).

- Connected DateTable with relationship DateTable[Date] to HistoricalInventory[Date].

I would like te create measures, and not a new big data table, as with all products, stores and dates, this will be an very very large table. The problem I'm facing, is mostly that I don't get the correct inventory levels for the days when there is no row entry available, as there were no changes in inventory for that specific day. The minimum inventory for a stock level with (multiple) row entries, is simple working. But for the days without row entries, it has to be the latest row entry before that day. This gives me blanks the whole time.
Example data from 1 article of 1 shop sorted on date
This is an example of the data filtered on 1 product from 1 store with it's stock levels. All products can have a different starting date, this one has 1st of september 2023. Ideally, I would like to have an output table with for example:
date                  article      store        minimum stock

01/09/2023       1197        60             27
02/09/2023       1197        60             26

03/09/2023       1197        60             26

04/09/2023       1197        60             26

05/09/2023       1197        60             26

06/09/2023       1197        60             26

07/09/2023       1197        60             50

08/09/2023       1197        60             45

09/09/2023       1197        60             44

10/09/2023       1197        60             43

etc.

But unfortunately I get:
Example of wrong output
I would be so pleased if someone can help me with this issue. And when this works, I can count the inventory levels per day per product per store, which gives insights in our inventory management. Thank you in advance!

[DataInsights · Accepted solution]

@Wonter,

Try this measure. It requires a date table with a 1:* relationship with the fact table HistoricalInventory. In the visual, use Dates[Date].

Minimum Stock = 
VAR vCurrentDate =
    MIN ( Dates[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( HistoricalInventory[history_date] ),
        Dates[Date] < vCurrentDate
    )
VAR vFilterPreviousDate =
    TREATAS ( { vPreviousDate }, Dates[Date] )
VAR vStockPreviousDate =
    CALCULATE ( MAX ( HistoricalInventory[stock] ), vFilterPreviousDate )
VAR vResult =
    IF (
        // if Dates[Date] does not exist in HistoricalInventory, use the last value
        ISEMPTY ( HistoricalInventory ),
        vStockPreviousDate,
        MAX ( HistoricalInventory[stock] )
    )
RETURN
    vResult

[DataInsights · Accepted solution]

@Wonter,

Try this measure. It requires a date table with a 1:* relationship with the fact table HistoricalInventory. In the visual, use Dates[Date].

Minimum Stock = 
VAR vCurrentDate =
    MIN ( Dates[Date] )
VAR vPreviousDate =
    CALCULATE (
        MAX ( HistoricalInventory[history_date] ),
        Dates[Date] < vCurrentDate
    )
VAR vFilterPreviousDate =
    TREATAS ( { vPreviousDate }, Dates[Date] )
VAR vStockPreviousDate =
    CALCULATE ( MAX ( HistoricalInventory[stock] ), vFilterPreviousDate )
VAR vResult =
    IF (
        // if Dates[Date] does not exist in HistoricalInventory, use the last value
        ISEMPTY ( HistoricalInventory ),
        vStockPreviousDate,
        MAX ( HistoricalInventory[stock] )
    )
RETURN
    vResult

[Wonter]

@DataInsights 

Thank you for your help. I tried your solution, with my DateTable with a 1:* relationship (single filter direction) with the fact table HistoricalInventory and also used DateTable[Date] in the visual, but unfortunately I got this results:

Missing dates without a row in the database, like 03/09, 04/09 etc.

What could be a reason that you get the correct result and I don't? Could it have to do something with the fact that there is (for example) no row with the date 03/09/2023 in the HistoricalInventory table for this specific article_id and kiosk_id? 

I also checked the option 'show items with no data' in the Visualizations section for the column Date, but it doesn't change anything unfortunately.

If I split up your measure in different measures, I get a table where all different kiosk_id's and all different article_id's are visible as well, even if I selected the last specific kiosk and article in this case. I splitted the measure also in smaller measures, to see what's happening. The measures are as following:

• minimum stock: complete measure as you mentioned
• p1: only first variable: return vCurrentDate
• p2: only 2 first variables: vCurrentDate and return vPreviousDate
• p3: only 3 first variables: vCurrentDate, vPreviousDate and return vFilterPreviousDate
• p4: only 4 first variables: vCurrentDate, vPreviousDate, vFilterPreviousDate and return vStockPreviousDate
• p5: all 5 variables again
  The results I get are:
  Results with all different article_id's and kiosk_id's and therefor also multiple dates

If I also use 'filters on this visual' with kiosk_id = 60 and article_id = 1197, I get the following results:
Results which for example 01/09/2023 and 07/09 and 08/09 there is no date in p1 and p2

Hopefully this helps it better to understand what's happening

[DataInsights]

@Wonter,

Thanks for the detailed feedback. In my example, there's no HistoricalInventory row for 03/09/2023. The issue appears to occur in "p2" (vPreviousDate is blank). Would you provide your DAX and a screenshot of your model (specifically these tables, including the relationship info)? If you have a sample model that you can share via OneDrive, etc. that would be helpful.

[Wonter]

@DataInsights Thank you for your quick response. I was creating an sample, and found out that as I was making the model easier (deleting tables), i got a different result:

p2 seems to work, but the rest not yet

So this looks better already. A screenshot of my sample model is here. I deleted a lot of tables and relations to get to this. But I can start building it again, if this works and see where it gets tricky.

Very simplified model

This is the Dax code from the measure you sent:

Last Actual Stock Minimum Stock =

VAR vCurrentDate =

    MIN ( DateTable[Date] )

VAR vPreviousDate =

    CALCULATE (

        MAX ( kiosks_historicalkioskarticle[history_date] ),

        DateTable[Date] < vCurrentDate

    )

VAR vFilterPreviousDate =

    TREATAS ( { vPreviousDate }, DateTable[Date] )

VAR vStockPreviousDate =

    CALCULATE ( MAX ( kiosks_historicalkioskarticle[stock] ), vFilterPreviousDate )

VAR vResult =

    IF (

        // if Dates[Date] does not exist in HistoricalInventory, use the last value

        ISEMPTY ( kiosks_historicalkioskarticle ),

        vStockPreviousDate,

        MAX ( kiosks_historicalkioskarticle[stock] )

    )

RETURN

    vResult

To be honest, I don't know how to upload a sample model here via OneDrive, as I only see the options for pictures, movies and links. Hopefully this can help. Thanks in advance!

[DataInsights]

@Wonter,

I recommend creating a star schema, which will enable you to remove bidirectional relationships from your model (unidirectional are best). You should be able to merge the tables kiosks_kiosk and kiosks_location in Power Query. You could call the merged table Kiosks. No relationship is needed between Kiosks and DateTable (dimension tables don't have relationships with each other in a star schema).

Once you upload a pbix to OneDrive, copy the link and paste the link in this post (there's a link icon).

[Wonter]

LINK

I hope this works @DataInsights. 

[DataInsights · Accepted solution]

@Wonter,

Thanks for the link. I found the issue. The variable vPreviousDate in the measure needs to use date, not historical_date (the relationship with DateTable is based on date, not historical_date.

VAR vPreviousDate =
    CALCULATE (
        MAX ( kiosks_historicalkioskarticle[date] ),
        DateTable[Date] < vCurrentDate
    )

[Wonter]

@DataInsights This works indeed, thank you very much! I really appreciate it.