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

1/3/18

Item A

5

1/5/18

Item A

20

1/1/18

Item B

10

1/5/18

Item B

15

 

And I have Table 2 with dates and items, and I need to indicate what the inventory is on each date for each item, like this:

 

Date

Item

Unit Total

1/1/18

Item A

10

1/1/18

Item B

10

1/2/18

Item A

10

1/2/18

Item B

10

1/3/18

Item A

5

1/3/18

Item B

10

1/4/18

Item A

5

1/4/18

Item B

10

1/5/18

Item A

20

1/5/18

Item B

15

 

I need to somehow lookup from the first table the value of Unit Total for the greatest date less than or equal to the given row and where the item is the same.

 

I can't figure out a simple way to do it. The direction I've taken has been to concatenate item and date on both tables ("Item-Date"), then use Lookup to pull in Unit Total for those dates where it exists:

 

Incomplete Totals = LOOKUPVALUE('Table 1'[Unit Total],'Table 1'[Item-Date],'Table 2'[Item-Date])

 

That gives me a column with values where they exist, and blanks for the rest.

 

Then I try to create a second column to fill in the blanks:

 

Daily Unit Total = CALCULATE(

    MAX('Table 2'[Incomplete Totals]),

    ALL('Table 2'),

    'Table 2'[Date] <= EARLIER('Table 2'[Date]),

    'Table 2'[Item] = EARLIER('Table 2'[Item])
)

 

Unfortunately, this formula takes the max Unit Total, rather than finding the Unit Total for the Max Date. So it works for Item B, which only increases, but doesn't decrease Item A when it goes from 10 to 5 on 1/3.

 

I'd love it if there's a better way to do the lookup so I can do it all in one step. Otherwise, I'd be interested to know how to structure my Calculate formula to get the Unit Total for just the max date less than the date in the current row, for the given item.

  • 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

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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

  • Hi,

     

    This is the calculated field formula i used

     

    =CALCULATE(MAX(inv_movement[Unit Total]),FILTER(inv_movement,inv_movement[Item]=EARLIER(inv[Item])&&inv_movement[Day]<=EARLIER(inv[Date])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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
        Super User

        Hi,

         

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