cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
davidhinckley Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Looking up values from a table with missing dates

Hi @davidhinckley,

 

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
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Looking up values from a table with missing dates

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])))

 

Untitled.png

davidhinckley Regular Visitor
Regular Visitor

Re: Looking up values from a table with missing dates

@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"

Community Support Team
Community Support Team

Re: Looking up values from a table with missing dates

Hi @davidhinckley,

 

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
        )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Looking up values from a table with missing dates

Hi,

 

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

davidhinckley Regular Visitor
Regular Visitor

Re: Looking up values from a table with missing dates

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

Super User
Super User

Re: Looking up values from a table with missing dates

Hi,

 

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