Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors