cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gwright15 Regular Visitor
Regular Visitor

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.  

 

3 REPLIES 3
gwright15 Regular Visitor
Regular Visitor

Re: Inventory Days - Count back method

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:

Purchases.png

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.

 

amitchandak Super Contributor
Super Contributor

Re: Inventory Days - Count back method

Check This

https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

Also running total in quick measures

gwright15 Regular Visitor
Regular Visitor

Re: Inventory Days - Count back method

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)