Forum Discussion
Adding missing date rows and filling in Quantity
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).