Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi, I have a table INVENTORY which has Inv_Date and the respective Quantity, as follows;
Inv_Date | Quantity |
01.01.2021 | 10 |
02.01.2021 | 10 |
05.01.2021 | 15 |
07.01.2021 | 100 |
I want to fill in the missing date rows and add the Quantity, as follows;
Inv_Date | Quantity |
01.01.2021 | 10 |
02.01.2021 | 10 |
03.01.2021 | 10 |
04.01.2021 | 10 |
05.01.2021 | 15 |
06.01.2021 | 15 |
07.01.2021 | 100 |
I have created a new table, and used the following DAX script:
FullDateTable =
ADDCOLUMNS(
CALENDAR(MIN(INVENTORY[Inv_Date]), MAX(INVENTORY[Inv_Date])),
"Quantity",
LOOKUPVALUE(
INVENTORY[Quantity],
INVENTORY[Inv_Date],
MAXX(
FILTER(INVENTORY, INVENTORY[Inv_Date] <= EARLIER([Inv_Date])),
[Inv_Date]
)
)
)
I am getting the error "Column 'Inv_Date' cannot be found or may not be used in this expression."
What am i missing here?
I think this is because CALENDAR generates a table with a column named [Date], not [Inv_Date].
Try using [Date] instead of [Inv_Date] inside of EARLIER or else use variables like this:
FullDateTable =
ADDCOLUMNS (
CALENDAR (
MIN ( INVENTORY[Inv_Date] ),
MAX ( INVENTORY[Inv_Date] )
),
"Quantity",
VAR CurrDate = [Date]
VAR LastInv_Date =
MAXX (
FILTER (
INVENTORY,
INVENTORY[Inv_Date] <= CurrDate
),
[Inv_Date]
)
RETURN
LOOKUPVALUE (
INVENTORY[Quantity],
INVENTORY[Inv_Date], LastInv_Date
)
)
Thanks for your suggestions.
I tried using [Date] with the EARLIER function and it gave me the error: "A table of multiple values was supplied where a single value was expected."
Also with the variables in your second suggestion, I got the same error: "A table of multiple values was supplied where a single value was expected."
It sounds like you're trying to define a measure or calculated column instead of a new table.
As a separate note, you may want to filter the calendar part if you only want one day per month. I.e.
FILTER (
CALENDAR (
MIN ( INVENTORY[Inv_Date] ),
MAX ( INVENTORY[Inv_Date] )
),
DAY ( [Date] ) = 1
)
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:
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.