Inventory changes using DAX - intra-location opening and closing dilemma

Hi roset03 

The end goal is partly for blending, but by far the biggest goal was to complete a complete value chain reconciliation framework linking Geology, Mining and Metallurgy. As you would know, stockpiles are generally a handover point between departments, and thus can prove to be the most difficult to manage. And for most reconciliation studies, the Delta in the stockpiles are what are needed to account for material that does not go directly from the Pit to the Plant. 

My solution was to do the following:

Create a separate table data (filtered) of intrastockpile movements from the Data table 

Data Filtered IntraStockpile =
FILTER (
    Data,
    LEFT ( Data[Unified_Origen], 6 ) = "Stock "
        && LEFT ( Data[Unified_Destination], 6 ) = "Stock "

Note above I created a "unified" identifier as the dispatch system for some reason has differing orders of taxonomy in naming sources and destinations. 

Then I created a dimension table which generated a list of the  stockpile ID and stockpile ID Detail ( I needed Stockpiles grouped into Oxide, Sulphide or Mixed)

tblStockpileIDUnique =
FILTER (
    SUMMARIZE ( data, Data[Stockpile_ID], Data[Stockpile_ID_Detail] ),
    Data[Stockpile_ID_Detail] <> ""
)

This dimension table was linked to the original data table and the filtered table - but it uses the following relationships:

data[Source] *---->tblStockpileIDUnique[Stockpile_ID_Detail] <----* Data Filtered IntraStockpile[Destination]

Tonnage In is calculated as 

Tonnage In = 
CALCULATE (
    SUM ( 'Data Filtered IntraStockpile'[TON_AJUSTADO] ),
    'Data Filtered IntraStockpile'[In_Code] = "OUT"
)
    + CALCULATE ( SUM ( 'Data'[TON_AJUSTADO] ), 'Data'[In_Code] = "IN" )

Tonnage out calculated as 

Tonnage Out = calculate(sum('Data'[TON_AJUSTADO]),'Data'[In_Code]="OUT")

Tonnage movement calced as:

Tonnage Movement = [Tonnage In]-[Tonnage Out]

And then openings and closings defined as :

Opening Stock Tonnes = 
VAR vLastDate = min('Data'[FECHA])
Return
CALCULATE ( [Tonnage Movement],
     FILTER (
         ALLEXCEPT ( Data, Data[Stockpile_ID],Data[Stockpile_ID_Detail] )
       ,'Data'[FECHA] < vLastDate )
    )


Closing Stock Tonnes = 
CALCULATE (
    [Tonnage Movement],
    FILTER (
        ALLEXCEPT ( Data, Data[Stockpile_ID], Data[Stockpile_ID_Detail] ),
        'Data'[FECHA] <= MAX ( 'Data'[FECHA] )
    )

(note Fecha means date in Spanish)
So basically the solve is to link the stockpiles to both the Source and Destination (using the two seperate tables) and use the the IN or OUT code to calculate additions and subtractions from the material movement.  There is a bit more to the setups that precede these steps, such as defining whether the material is moving IN or OUT ( as per the diagram above) but that is fairly easy. 
IM me on LinkedIn if you need futher information - happy to discuss if you need any further assistance assistance. 

Cheers

Manfred