Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 @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])))
@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"
Hi,
What happens when you replace MAX(inv_movement[Unit Total]) with LASTNONBLANK(inv_movement[Unit Total],1)
@Ashish_Mathur, logicially it feels like LASTNONBLANK should work, but it had the same result as MAX.
Hi,
Please show the expected result. Especially for the case then the inventory level falls.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |