Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Looking up values from a table with missing dates

I have a Table 1 that tracks increases and decreases in inventory, but only has line items for the days where the inventory changes:   Day Item Unit Total 1/1/18 Item A 10 ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try this formula:

    Unit Total =
VAR Mostrecentday =
    CALCULATE (
        MAX ( Table1[Day] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] <= EARLIER ( Table2[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Unit Total] ),
        FILTER (
            Table1,
            Table1[Item] = EARLIER ( Table2[Item] )
                && Table1[Day] = Mostrecentday
        )
    )

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ashish_Mathur, this gets me slightly closer, giving me a value for each date without having to use two columns.

 

Unfortunately, the logic is still essentially faulty. It's saying: "Give me the max unit count for all dates on or before the current date for this current item." That gives me the wrong value if the unit count decreases. What I need is: "Give me the most recent unit count for this item on or before the current date"

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

What happens when you replace MAX(inv_movement[Unit Total]) with LASTNONBLANK(inv_movement[Unit Total],1)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Ashish_Mathur, logicially it feels like LASTNONBLANK should work, but it had the same result as MAX.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      Please show the expected result.  Especially for the case then the inventory level falls.