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_Date Quantity 01.01.2021     10 02.01.2021     10 05.01.2021     15 07.01.2021    ...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper 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
    )

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

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