Adding missing date rows and filling in Quantity

[samurai_jack]

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]

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
            )
)

[samurai_jack]

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]

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
    )

[samurai_jack]

I created a table with the original query (Modeling > New table).

[AlexisOlson]

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.

[samurai_jack]

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:

LatestSysytemLoadID =

CALCULATE (

MAX (INVENTORY[System_Load_ID]),

ALLEXCEPT (INVENTORY, INVENTORY[Component], INVENTORY[Inv_Date])

)

Thanks in advance!

[AlexisOlson]

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
            )
)

[samurai_jack]

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

[AlexisOlson]

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.