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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
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