Forum Discussion
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-msftMicrosoft 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
- Ashish_MathurSuper User
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])))
- AnonymousNot 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_MathurSuper User
Hi,
What happens when you replace MAX(inv_movement[Unit Total]) with LASTNONBLANK(inv_movement[Unit Total],1)