Forum Discussion

Wonter's avatar
Wonter
Frequent Visitor
2 years ago
Solved

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

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 ...
Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    2 years ago

    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's avatar
    DataInsights
    2 years ago

    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
    )

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
2 years ago

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's avatar
    Wonter
    Frequent Visitor
    2 years ago

    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's avatar
      DataInsights
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        Wonter
        Frequent Visitor
        2 years ago

        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!