cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samurai_jack
Helper I
Helper I

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?

9 REPLIES 9
AlexisOlson
Super User
Super User

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:

LatestSysytemLoadID =
CALCULATE (
MAX (INVENTORY[System_Load_ID]),
ALLEXCEPT (INVENTORY, INVENTORY[Component], INVENTORY[Inv_Date])
)
 
Thanks in advance!

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
72605A19.08.2021   US
72605A23.08.2021   US
0120C19.08.2021 China
0605B19.08.2021 China
10120C23.08.2021 China
0605B23.08.2021 China
10120C24.08.2021 China
0605B24.08.2021 France
0120C30.08.2021 France
0605B30.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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors