Consumption of Product based on Next Entry

[Anonymous]

I am trying to figure out how to calculate how much a product is consumed knowing a constant rate of consumption and the estimated number of days it will take. My goal is to take this and determine if the customer needs to reorder or if they are so far gone that they should be considered 'churn.' This is how my data is structured:

CustomerID_ProductID	Product_Name	Days Product Lasts	Date_Sold	ProductID_Sold_Index	% Consumed
123_ABC	Napkins	30	12/1/2019	00	100%
123_ABC	Napkins	30	12/31/2019	01	100%
123_ABC	Napkins	30	1/30/2020	02	5%
456_ABC	Napkins	60	12/15/2019	00	75%
789_DEF	Water Bottles	30	10/1/2019	00	100%
789_DEF	Water Bottles	30	11/1/2019	01	300%

What I'm trying to do is take the customers and their products and identify those that are coming up on when they need to order with us (should we email them?) and customers who haven't ordered with us for some time (last row) as potential re-engagements etc. If they already placed their second or third order (see Index), I want their prior order to default to 100% consumption.

Thoughts? Ideas on how to do this? Would this be better as a measure rather than a column? Thanks for your help!

[Anonymous · Accepted solution]

Hi @Anonymous  If you are looking for % consumed measure, please use bleow measure.

Measure = 
VAR _maxIndex = CALCULATE(MAX('Table'[ProductID_Sold_Index]),ALLEXCEPT('Table','Table'[CustomerID_ProductID]))
VAR _dayssincelastpurchase = DATEDIFF(SELECTEDVALUE('Table'[Date_Sold]),TODAY(),DAY)
RETURN IF(SELECTEDVALUE('Table'[ProductID_Sold_Index])<_maxIndex,1,DIVIDE(_dayssincelastpurchase,SELECTEDVALUE('Table'[Days Product Lasts]),0))

[Anonymous]

Thanks @Anonymous!! Your solution got me 80% of the way there and I was able to figure out the rest based on what you gave me!

For those curious as to why it wasn't a 100% solution - our business receives new products from different manufacturers that are essentially the same as existing products (same name, usage). These new products have a new Product ID assigned to them - so the Napkins in my example would be Product ID 123456789 and a new Napkins Product ID might be 124456789. This was causing issues since the new ID would generate a new order Index number, meaning the old product wouldn't see the new order and mark it as 500% consumption. Using just the name (Napkins) would throw an error of 100% for the new product when it was in fact only at 50%.

Either way, my solution is below - I had to bring in the actual Product_ID (which is sequential in our system) and create a new field for CustID_Product_Name to enable comparison based on Product Names:

Consump% = 
VAR _maxIndex =             CALCULATE( MAX( 'Table'[ProductID_Sold_Index] ) , ALLEXCEPT('Table' , 'Table'[CustID_Product_Name] ))
VAR _dayssincelastfill =    DATEDIFF( 'Table'[Date_Sold] , TODAY() , DAY )
VAR _maxIDNum =             CALCULATE( MAX( 'Table'[Product_ID] ) , ALLEXCEPT( 'Table' , 'Table'[CustID_Product_Name] ) )
Return 
    IF( _maxRxNum > 'Table'[Product_ID] , 1 ,
        IF(  'Table'[ProductID_Sold_Index]  < _maxIndex , 1 , DIVIDE( _dayssincelastfill , 'Table'[Days Product Lasts]  , 0 )) ) 

Thanks again for the help!