Adding missing date rows and filling in Quantity

Question

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?

alexisolson · Answer

I think this is because CALENDAR generates a table with a column named [Date], not [Inv_Date].
&nbsp;
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] &lt;= CurrDate
                ),
                [Inv_Date]
            )
        RETURN
            LOOKUPVALUE (
                INVENTORY[Quantity],
                INVENTORY[Inv_Date], LastInv_Date
            )
)

anonymous · Answer

Thanks for your suggestions.&nbsp;I tried using&nbsp;[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:&nbsp;"A table of multiple values was supplied where a single value was expected."

alexisolson · Answer

It sounds like you're trying to define a measure or calculated column instead of a new table.
&nbsp;
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
    )
&nbsp;

anonymous · Answer

I created a table with the original query (Modeling &gt; New table).

alexisolson · Answer

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.

Forum Discussion

Adding missing date rows and filling in Quantity