none
Inventory Age Structure Analysis

    Question

  • Hi,

    I am using PowerPivot of Excel 2013 and trying to accomplish an inventory age structure analysis.

    I have got a snapshot table "stock_table" with daily updates for products on stock and a custom date table. The columns of the stock table are as follows:

    • date
    • product_key
    • units

    I'd like to show a summary of products which are let's say <=30, >30 and <=90 days on stock. Products which are at query date not any more on stock should be excluded from the summary.

    Example:

    stock_table:

    • columns: date, product_key, units
    • 01/31/2014, 4711, 200
    • 02/28/2014, 4711, 200
    • 03/31/2014, 4711, 100 (last entry, no units left at 04/03/2014)
    • 01/31/2014, 4712, 100
    • 02/28/2014, 4712, 100
    • 03/31/2014, 4712, 100
    • 04/03/2014, 4712, 100
    • 03/31/2014, 4713, 300
    • 04/03/2014, 4713, 300

    Age analysis as per 04/03/2014 should show:

    • stock age <=30 days: 4713, 300 units
    • stock age >30 days and <=90 days: 4712, 100 units
    • 4711 should not show up because it has zero units as per 04/03/2014

    Can anyone give me a hint how to accomplish this analysis via dax?

    Thanx

    Chiemo

    Thursday, April 03, 2014 4:06 PM

Answers

  • Hi,

    I just uploaded a possible solution to http://files.gbrueckl.at/public/Inventory.xlsx

    anyway, you may also consider a different approach for your scenario
    it seems that you store the same unit-count everyday which results in a lot of duplicate rows

    instead of storing a row for each day you would only store one row containing startdate and enddate and then use an Events In Progress calculation

    there is a very good whitepaper of Alberto Ferrari on how to deal with Events in Progress:
    http://www.sqlbi.com/articles/understanding-dax-query-plans

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, April 07, 2014 3:27 PM

All replies

  • Hi Chiemo,

    It's very likely that you can adapt the DAX pattern described here by Marco Russo. If you need help applying it to your scenario, could you kindly provide a link to an Excel workbook (perhaps uploaded to Dropbox or OneDrive) with some test data so that a member of the community or I can show you how?


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Friday, April 04, 2014 10:23 AM
  • Hi,

    I just uploaded a possible solution to http://files.gbrueckl.at/public/Inventory.xlsx

    anyway, you may also consider a different approach for your scenario
    it seems that you store the same unit-count everyday which results in a lot of duplicate rows

    instead of storing a row for each day you would only store one row containing startdate and enddate and then use an Events In Progress calculation

    there is a very good whitepaper of Alberto Ferrari on how to deal with Events in Progress:
    http://www.sqlbi.com/articles/understanding-dax-query-plans

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, April 07, 2014 3:27 PM
  • Hi Michael,

    thank you very much for the link and your offer to help.

    Actually, I know the article by Marco Russo on segmentation/banding and was able to implement the concept successfully in some powerpivot workbooks, such as banding for margin and customer groups.

    The challenge for me in the stock structure analysis subject is actually at the beginning on how to build a calculated column that delivers the different stock age day bands. I had the following idea: calculate for any stock item the first date and calculate the last date when the item showed up in the stock snapshot table. Then I think I could calculate the stock age in days. Unfortunately, I failed to calculate both the first and the last date of a specific item. That's my problem.

    The snapshot table has about 40 million data records and I actually don't know how to export from power pivot only a fraction of the snapshot table. Therefore, I am very sorry that I am not able to provide you real life data (Is there a possibility?). Sorry!

    Again thank you very much for your answer. I very much like the "Italians" too.

    Regards,

    Chiemo


    • Edited by Chiemo Monday, April 07, 2014 7:48 PM
    Monday, April 07, 2014 7:46 PM
  • Hi Gerhard,

    thank you very much for providing the file. I just downloaded it and will look into it and then come back to you.

    Again, thank you very much!

    Regards,

    Chiemo

    Monday, April 07, 2014 7:52 PM
  • Ok, no worries. Gerhard's solution looks like a good fit :)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Tuesday, April 08, 2014 11:37 AM
  • Hi Gerhard,

    thank you very much for your excellent solution. Based on your file it was easy to adapt your solution to my real data.

    One tiny and really minor point for other users of Gerhard's solution from my side to one formula where I got with my real data an error message: please replace in the formula of the calculated column "StockAge" in the table "Inventory" the "<=" sign in the fourth row by a "<" sign. The formula that worked for me is

    StockAge=CALCULATE(VALUES(StockAge[Stockage]);

    FILTER(

        'StockAge';

        StockAge[StockMin] < [StockAgeDays]

        && [StockAgeDays] <= StockAge[StockMax]))

    Thank you again, Gerhard!

    Best regards

    Chiemo


    • Edited by Chiemo Tuesday, April 08, 2014 8:05 PM
    Tuesday, April 08, 2014 8:03 PM