Forum Discussion

BugmanJ's avatar
BugmanJ
Helper V
1 year ago

Getting the Latest Date and Inventory Level

Hello All, I have the following setup: CakeShop: Columns: SName, ShopID CakeCategory: Columns: CCategory, SortIndex CakeManufacturer: Columns: CManufacturer, SortIndex CakeNam...
Need Help
DallasBaba's avatar
DallasBaba
Skilled Sharer
1 year ago

BugmanJ Can you adjust the filter context to avoid incorrect CName and CCategory combinations?

InventoryDateMeasure = 
CALCULATE(
    MAX(CakeInventory[InventoryDate]),
    FILTER(
        CakeInventory,
        CakeInventory[ShopID] = SELECTEDVALUE(CakeShop[ShopID]) &&
        CakeInventory[CCategory] = SELECTEDVALUE(CakeCategory[CCategory])
    )
)

InventoryMeasure = 
CALCULATE(
    MAX(CakeInventory[Inventory]),
    CakeInventory[InventoryDate] = [InventoryDateMeasure],  // This will ensure you get the inventory for the latest date
    FILTER(
        CakeInventory,
        CakeInventory[ShopID] = SELECTEDVALUE(CakeShop[ShopID]) &&
        CakeInventory[CCategory] = SELECTEDVALUE(CakeCategory[CCategory])
    )
)

 

If the result still show unwanted combinations of CName and CCategory, you can use HASONEVALUE() to ensure only relevant CName values are considered.

CALCULATE(
    MAX(CakeInventory[Inventory]),
    FILTER(
        CakeInventory,
        CakeInventory[ShopID] = SELECTEDVALUE(CakeShop[ShopID]) &&
        CakeInventory[CCategory] = SELECTEDVALUE(CakeCategory[CCategory])
    ),
    IF(HASONEVALUE(CakeName[CName]), TREATAS(VALUES(CakeName[CName]), CakeSales[CName]))
)

 

I hope this help, else please @ me with sample of your pbix file