Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Adding missing date rows and filling in Quantity

Hi, I have a table INVENTORY which has Inv_Date and the respective Quantity, as follows;

 

Inv_DateQuantity
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_DateQuantity
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?

Need Help

9 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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
            )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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."

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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
    )