Inventory Allocation

[Anonymous]

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.

[AlB]

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]

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]

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.

[Anonymous]

kumar27

Thank you for the insight on this being able to be done in query editor but I am really new with this stuff and am having an issue on how I would do the calulated column in query editor when they are to differnt tables.