Forum Discussion
Adding missing date rows and filling in Quantity
I created a table with the original query (Modeling > New table).
If you're indeed creating a calculated table, then the error likely means that there are [Inv_Date] associated with multiple values of [Quantity], which causes LOOKUPVALUE to fail since there isn't a unique value to return.
hi again,
Yes I found the issue, thanks to your suggestion. There are multiple Inv_Date entries for a particular date. These entries have an associated System_Load_ID, and the goal is to take the Inv_Date with the highest System_Load_ID.
My question is: how do I set my filter into this query.
Previously I was using a similar filter as a column:
LatestSysytemLoadID =CALCULATE (MAX (INVENTORY[System_Load_ID]),ALLEXCEPT (INVENTORY, INVENTORY[Component], INVENTORY[Inv_Date]))Thanks in advance!You could add that as another lookup condition.
FullDateTable = ADDCOLUMNS ( FILTER ( CALENDAR ( MIN ( INVENTORY[Inv_Date] ), MAX ( INVENTORY[Inv_Date] ) ), DAY ( [Date] ) = 1 ), "Quantity", VAR CurrDate = [Date] VAR LastInv_Date = MAXX ( FILTER ( INVENTORY, INVENTORY[Inv_Date] <= CurrDate ), [Inv_Date] ) VAR LatestSysytemLoadID = CALCULATE ( MAX ( INVENTORY[System_Load_ID] ), INVENTORY[Inv_Date] = LastInv_Date ) RETURN LOOKUPVALUE ( INVENTORY[Quantity], INVENTORY[Inv_Date], LastInv_Date, INVENTORY[System_Load_ID], LatestSysytemLoadID ) )One last question (sorry, I am still a DAX rookie, but learning). If I have multipe Component part numbers and Plants to account for in the data, like in the table below, how would I incorporate this into the query;
Quantity Component Inv_Date Plant 72 605A 19.08.2021 US 72 605A 23.08.2021 US 0 120C 19.08.2021 China 0 605B 19.08.2021 China 10 120C 23.08.2021 China 0 605B 23.08.2021 China 10 120C 24.08.2021 China 0 605B 24.08.2021 France 0 120C 30.08.2021 France 0 605B 30.08.2021 France