Power Pivot/Query: Cumulative inventory based on future sales

Hi,

Currently your Inventory table is neither used in the Pivot Table nor added to the Data Model, so I'm not sure why you mention that.

Also, in your file you state "this would show 33 (55-2-24)", though I believe your arithmetic is wrong there!

Try this measure:

PHP Code: 

Net Inventory :=
SUM( Sales[Available] )
    - CALCULATE(
        SUM( Sales[Qty] ),
        FILTER(
            ALLEXCEPT( Sales, Sales[SKU] ),
            Sales[Order #]
                IN { "O1", "O2" }
                    && Sales[Ship Date] <= MIN( Sales[Ship Date] )
        )
    ) 


though you need to clarify what the results should be for O3.

Regards

Please try

Please Login or Register  to view this content.

XOR LX, the inventory table is used to bring the inventory into the sales table.
I am not sure if this is the best way to go about this but this is what I have done.
You are right, my arithmetic was incorrect in the file. That should have been 29 not 33.
I can't hardcode O1, O2, O3, etc. since there are so many and it will be changing.
In this example, O1 and O2 pull from the same warehouse (as shown in the sales table) and O3 does not so the inventory depends on what warehouse the SKU is being pulled from.

Bo_Ry, this is exactly what I am looking for, however I was not able to successfully implement it into my file.
I made a more representative sample of my actual data and have attached it here.
Once I see that this is working as is, I will be inserting "Your Ref. (Sold-To)" in the pivot between sales rep and SKU, but I did not include that in this sample to keep it cleaner (and I don't think that will change anything).

I want to be able to collapse the pivot table and have the inventory for the different SKUs add up correctly without duplicating.

To illustrate the issue, I filtered the pivot by a single SKU.

Your help is greatly appreciated as always!

Sorry, I'm not able to find the Data model context logic to make
Rep3 with only WC to include qty from Rep1 with WA, WC

You need to create Calendar table to make total running work on date

PHP Code: 

=
VAR D =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        SUM ( Orders[Qty] ),
        MAX ( Orders[Inventory] )
            - CALCULATE (
                SUM ( Orders[Qty] ),
                ALL ( Orders[Sales Rep. Name] ),
                Orders[Warehouse]
                    IN CALCULATETABLE ( VALUES ( Orders[Warehouse] ), ALL ( 'Calendar'[Month] ) ),
                'Calendar'[Date] <= D
            )
    )