Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Inventory Allocation

Looking for some table and report help on how to make a Inventory Allocation report to help us ship inventory to oldest order first.

 

I have an inventory onhand report and I would like to take the onhand amount and subtract it from oldest orders until inventory goes negative.  I also have an orders report that container ordered dated, order number, sku ordered and qty ordered.

 

Currently I do this in Excel by sorting orders by oldest date and then smallest order number.  Then taking inventory ohand and using the formula  J2 - G3 to start off then for the rest use formual in column h as "Example H4-G4"

 

Doing this for 100 sku's in excel takes forever and I would like to see if there is away to do this with DAX in BI.  Again I have a table with Onhand Inventory and one table with Orders.

Inventory

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    Can you show samples of both tables in text-tabular format (so that the contents can be copied)? And based on that sample data, show the expected result detailing the logic to get there.

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Im not sure exactly what you are asking for but here is what the to tables look like in BI.

       

       

      I hope this helps.

      • kumar27's avatar
        kumar27
        Advocate V
        5 years ago

        As you said

         

        Currently I do this in Excel by sorting orders by oldest date and then smallest order number.  Then taking inventory ohand and using the formula  J2 - G3 to start off then for the rest use formual in column h as "Example H4-G4"

         

        The same thing can be done in Power BI query editor where the columns can be sorted and then you can add the calculated column to get H4-G4.