Dax calculation for inventory age on hand

[jeannier2022]

I have a Power bi Dashboard that I have Inherited the ownership and it appears that the Dax calculation is not working right, The values for the age buckets should not be the same accross the board.

OH INVENTORY - EQ QTY_2 = 
VAR PRIMARYUOMFILTER = FILTER('DYNAMIC INVENTORY DATA',[PRIMARY UOM]="BIN" || [PRIMARY UOM]="CTN" || [PRIMARY UOM]="PLT")
RETURN CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]),PRIMARYUOMFILTER)

[saud968]

You should have a column in your 'DYNAMIC INVENTORY DATA' table that represents the age of each inventory item. If you don't have this, you'll need to create it.
Then, create a new measure that calculates the inventory quantity for each age bucket. Here's an example:

daxCopyInventory by Age =
VAR CurrentDate = TODAY()
VAR PRIMARYUOMFILTER = FILTER('DYNAMIC INVENTORY DATA', [PRIMARY UOM] IN {"BIN", "CTN", "PLT"})
RETURN
SWITCH(
TRUE(),
[INVENTORY AGE GROUPING] = "0-7 DAYS", CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]), PRIMARYUOMFILTER, 'DYNAMIC INVENTORY DATA'[InventoryDate] >= CurrentDate - 7),
[INVENTORY AGE GROUPING] = "8-15 DAYS", CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]), PRIMARYUOMFILTER, 'DYNAMIC INVENTORY DATA'[InventoryDate] >= CurrentDate - 15 && 'DYNAMIC INVENTORY DATA'[InventoryDate] < CurrentDate - 7),
[INVENTORY AGE GROUPING] = "16-23 DAYS", CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]), PRIMARYUOMFILTER, 'DYNAMIC INVENTORY DATA'[InventoryDate] >= CurrentDate - 23 && 'DYNAMIC INVENTORY DATA'[InventoryDate] < CurrentDate - 15),
[INVENTORY AGE GROUPING] = "24-31 DAYS", CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]), PRIMARYUOMFILTER, 'DYNAMIC INVENTORY DATA'[InventoryDate] >= CurrentDate - 31 && 'DYNAMIC INVENTORY DATA'[InventoryDate] < CurrentDate - 23),
[INVENTORY AGE GROUPING] = "31+ DAYS", CALCULATE(SUM('DYNAMIC INVENTORY DATA'[ON HAND EQUIVALENT QUANTITY]), PRIMARYUOMFILTER, 'DYNAMIC INVENTORY DATA'[InventoryDate] < CurrentDate - 31),
BLANK()
)
This measure does the following:

It uses the INVENTORY AGE GROUPING column to determine which age bucket we're calculating for.
For each bucket, it calculates the sum of ON HAND EQUIVALENT QUANTITY for items that fall within that date range.
It still applies the filter for the primary units of measure (BIN, CTN, PLT).

To implement this:

Replace the existing measure in your report with this new one.
Make sure the INVENTORY AGE GROUPING column in your table matches the age buckets in the DAX measure.
Ensure you have a date column (InventoryDate in this example) that represents when each inventory item was received or last updated.

If you're still experiencing issues after implementing this, it would be helpful to:

Verify the data in your 'DYNAMIC INVENTORY DATA' table to ensure it contains varied dates for the inventory items.
Check if there are any filters applied to the report that might be affecting the results.
Confirm that the INVENTORY AGE GROUPING column is correctly categorizing the inventory based on its age.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!