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...
Need Help
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.

 

  • gwright15's avatar
    gwright15
    Helper I
    6 years ago

    Thanks for this.  My running total seems to be working okay.

     

    The element i'm struggling with is to ascertain which date the running total gets breached.

     

    I've tried using LASTNONBLANK but doesn't seems to be working.