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:

  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:
    CCategoryCNameInventoryDateMeasureShopNameInventoryMeasure
    HeavyFudge10th Oct 24East50
     Fruit11th Oct 24West5
      18th Oct 24North25

    But I get this:

    CCategoryCNameInventoryDateMeasureShopNameInventoryMeasure
    HeavyFudge10th Oct 24East50
     Fruit11th Oct 24West5
      18th Oct 24North25
     Sponge07th Oct 24West15
     Light Sponge12th Oct 24South17
    LightFudge10th Oct 24East50

    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

Need Help

2 Replies

  • 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