Getting the Latest Date and Inventory Level

[BugmanJ]

Hello All,

I have the following setup:

1. CakeShop:

   • Columns: SName, ShopID

2. CakeCategory:

   • Columns: CCategory, SortIndex

3. CakeManufacturer:

   • Columns: CManufacturer, SortIndex

4. CakeName:

   • Columns: CName, SortIndex

5. CakeSales:

   • Columns: ShopID, CCategory, CManufacturer, CName, CakeSales, CakeSalesDate

6. CakeInventory:

   • Columns: ShopID, CCategory, Inventory, InventoryDate

Relationships

• CakeShop is linked to CakeSales and CakeInventory via ShopID.
• CakeCategory is linked to CakeSales and CakeInventory via CCategory.
• CakeName is linked only to CakeSales via CName.
  
  I have a matrix with CCategory (From CakeCategory) and CName (From CakeName) and SName (From CakeShop) and in the values area, i have various measures around CakeSalesDate
  
  What I want to show is the latest InventoryDate and hence the Latest Inventory that matches Category and SName.
  
  Whilst CName is not available in CakeInventory, there is only one CName for Each CCategory/SName variety
  
  I can't get this to work due to CName because as soon as i put any measure in, the CName / CCategory falls apart and CCategory starts having CNames combos that dont exist. In addition, I have tried using a date calander but it doesnt seem to work either.
  
  I want to have something like this as an example:
  

  CCategory	CName	InventoryDateMeasure	ShopName	InventoryMeasure
  Heavy	Fudge	10th Oct 24	East	50
  	Fruit	11th Oct 24	West	5
  		18th Oct 24	North	25

  
  But I get this:
  
  

  CCategory	CName	InventoryDateMeasure	ShopName	InventoryMeasure
  Heavy	Fudge	10th Oct 24	East	50
  	Fruit	11th Oct 24	West	5
  		18th Oct 24	North	25
  	Sponge	07th Oct 24	West	15
  	Light Sponge	12th Oct 24	South	17
  Light	Fudge	10th Oct 24	East	50

  
  InventoryDateMeasure = 
  
  CALCULATE(

  MAX(CakeInventory[InventoryDate]),

  FILTER(

  CakeInventory,

  CakeInventory[ShopID] = SELECTEDVALUE(CakeShop[ShopID]) &&

  CakeInventory[CCategory] = SELECTEDVALUE(CakeCategory[CCategory])

  ),

  TREATAS(VALUES(CakeManufacturer[CManufacturer]), CakeManufacturer[CManufacturer])

  )
  
  
  InventoryMeasure = 
  
  CALCULATE(

  MAX(CakeInventory[Inventory]),

  CakeInventory[InventoryDate] = [InventoryDateMeasure],

  FILTER(

  CakeInventory,

  CakeInventory[ShopID] = SELECTEDVALUE(CakeShop[ShopID]) &&

  CakeInventory[CCategory] = SELECTEDVALUE(CakeCategory[CCategory])

  ),

  TREATAS(VALUES(CakeManufacturer[CManufacturer]), CakeManufacturer[CManufacturer])

  )
  
  (The later one fails with a Placeholder function being used as a Filter Expression which isnt allowed)
  
  Example Demo File - https://filebin.net/37c8u0sh8hid3dfq
  Regards
  
  

[BugmanJ]

Hi @DallasBaba 
Sorry the above doesnt work.
I do now have a demo file, you can find it here https://filebin.net/37c8u0sh8hid3dfq

Thanks

[DallasBaba]

@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