Forum Discussion
Adding missing date rows and filling in Quantity
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:
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 If you have multiple rows per date, then this can't be done by adding columns to a date column with one row per date.
You could do a crossjoin of dates and components and then add columns but it might be better to stick to your original data table and use measures to fill the holes as needed rather than interpolating a complete crossjoin.