Forum Discussion

gwright15's avatar
gwright15
Helper I
6 years ago

Inventory Days - Count back method

Hello

 

I'm looking to calculate the inventory days of each product line, using the count back method.

 

So for example, if i have 1000 units in stock, a bought 300, 30 days ago, 500, 50 days ago, and 300, 105 days ago, the stock ageing would be 105 days.

 

The data i currently have:

Stock quantity at any given time, calculated via another measure.

A table with all the purchasing history, by product code, and by date.

A date table

 

The Stock table is linked to the Purchasing data via the Product Code.  

 

Need Help

3 Replies

  • gwright15's avatar
    gwright15
    Helper I
    6 years ago

    So i've made alittle progress.

     

    I've added a measure to calculate a running reserver cumulative quantity - 

    Reverse Cumulative Quantity = 
CALCULATE(sum('Purchasing Data'[quan]),
Filter(ALLSELECTED('Date'),'Date'[Date]>=MIN('Date'[Date])))

    This shows the following:

    This shows that 913.44 was purchased on 28 June 2019, and a further 996.48 was purchased on 8 May 2019.

     

    My currently quantity in stock is 1,306.63, therefore want to report the date 8 May 2019.

     

    However, my attempt has been unsuccessful:

     

    Date_Target_Reached = 
IF (
    HASONEVALUE ( Stock_Record[stcode] ),
    VAR Stock_Quantity =
        [Quantity]
    VAR FirstDateOverTarget =
        FIRSTNONBLANK (
            'Purchasing Data'[deldate],
            IF (
                [Reverse Cumulative Quantity] >= Stock_Quantity,
                1
            )
        )
    RETURN
        FirstDateOverTarget
)

    Any help would be greatly appreciated.