Forum Discussion

MarekV's avatar
MarekV
Frequent Visitor
6 years ago

Inventory Aging - dynamicaly calculated inventory aging

Hi,
I'm trying figure out, how dynamicaly calculate inventory age to some date in the past.
I want see in my report in January 2019 was inventory age 200 days, in February 228 days atc.
I have table with all inventory transactions where is TransactionDate and DateOfReceipt so for each transaction I'm able calculate age  as  TransactionDate - DateOfReceipt

It's no problem calculate with weighted average inventory age to actual date, but problem (for me) is calculate it for previous periods and see inventory age trend

first I'm trying calculate Maximum date (for calculated period)

_01 DateMax =
VAR MaxDate =  CALCULATE(MAX('DateA'[DateA]); USERELATIONSHIP( DateA[DateA]; DateB[DateB] ))
RETURN
IF
(
       MaxDate > TODAY();
       TODAY();
       MaxDate
    )

next is 
_02 TransactionAgeInDays =
VAR TransactionAge = DATEDIFF(MIN(InventoryTransactions[DateOfReceipt]);InventoryTransactions[_01 DateMax];DAY)
RETURN
IF( TransactionAge > 0; TransactionAge; 0)
Next
_03 TransactionQuantityCumulated =

IF (
    MIN ( 'DateA'[DateA] )
        <= CALCULATE ( MAX ( InventoryTransactions[TransactionDate] ); ALL ( InventoryTransactions ) );

VAR MaxDate = MAX ( 'DateA'[DateA] ) -- Saves the last visible date
VAR QuantityTotal = SUM(InventoryTransactions[TransactionQuantity])

RETURN
    CALCULATE (
        QuantityTotal;  -- Computes sales amount
        'DateA'[DateA] <= MaxDate;   -- Where date is before the last visible date
        ALL ( DateA )               -- Removes any other filters from Date
    ))
 

and last one but with wrong results (for me) 🙂

_04 weighted average =
        SUMX(InventoryTransactions; InventoryTransactions[TransactionQuantity] * InventoryTransactions[_02 TransactionAgeInDays])
/ InventoryTransactions[_03 TransactionQuantityCumulated]


see picture

bix file is here https://1drv.ms/u/s!AoIu5I4KBoBIyyQaE20m4Y3qJGIz?e=V3Fmwq 
 
can someone help me please calculate it correct way?

Thanks 
marek

Need Help