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

Question

&nbsp;Hi Folks&nbsp;I have a interesting problem to solve. It involves calculating opening and closing balances for inventory locations or stockpiles. The operation has a material tracking system which tracks material moved from&nbsp; source and to destinations for an open pit operation. In most cases, the opening and closing balances are solved using the following measures:&nbsp;Opening Stock Tonnes =
CALCULATE ( [Tonnage Movement],
     FILTER (
         ALLEXCEPT ( Data, Data[Stockpile_ID],Data[Stockpile_ID_Detail] )
       ,'Data'[Date] &lt; min('Data'[Date])
    )

Closing Stock Tonnes = 
CALCULATE (
    [Tonnage Movement],
    FILTER (
        ALLEXCEPT ( Data, Data[Stockpile_ID] ),
        'Data'[Date] &lt;= MAX ( 'Data'[Date] )
    )
)

Tonnage Movement =
CALCULATE ( SUMX ( 'Data', 'Data'[Tonnes] ), 'Data'[In_Code] = "IN" )
    - CALCULATE ( SUMX ( 'Data', 'Data'[Tonnes] ), 'Data'[In_Code] = "OUT" )This all works fine until there are intrastockpile movements - or movements between stockpiles. It shouldn't occur - but reality is of course at work.&nbsp;The problem is summarized in the following pictures:The issue occurs at the exclamation point ! above - in that Stockpile C has tonnes subtracted, Stockpile D has tonnes added, all in one transaction line in the database. My "In_Code" column isn't able to obviously handle both an "In" and "Out" code simulaneously.The above spreadsheet is an attempt to show in formulas what should&nbsp; happen to the tonnages.&nbsp;Does anyone have an idea to handle this elegantly in DAX? I have about 1.4M rows of transactions and I know I need to do some optimising on the calcs ( I think my SUMX iterators are possibly slowing things down) plus the fact the users want this information in a matrix table which is also adding to overheads. But first things first - how to handle the intra-stockpile movements? 🤷‍♂️😥Any help would be hugely appreciated. Been trying /struggling with USERELATIONSHIP but I was getting out of memory issues, and I am flat out of hair to pull out.&nbsp;Thanks in advanceManfred

mwimberger · Answer

Hi Tim&nbsp;I did manage to solve this. My initial solution was to use USERELATIONSHIP, but this wasn't completely without issue as it seemed to slow things down.What I then settled on was a second referenced query ( subset of the the initial data)&nbsp; and a relationship of this query to the Distinct list of locations ( in and outs)&nbsp;I will detail exactly how I did this with a longer post soon. Good to see some mining related interest out there!&nbsp;Cheers&nbsp;Manfred

mwimberger · Answer

Hi Folks&nbsp;I have a interesting problem to solve. It involves calculating opening and closing balances for inventory locations or stockpiles. The operation has a material tracking system which tracks material moved from&nbsp; source and to destinations for an open pit operation. In most cases, the opening and closing balances are solved using the following measures:&nbsp;Opening Stock Tonnes =
CALCULATE ( [Tonnage Movement],
     FILTER (
         ALLEXCEPT ( Data, Data[Stockpile_ID],Data[Stockpile_ID_Detail] )
       ,'Data'[Date] &lt; min('Data'[Date])
    )

Closing Stock Tonnes = 
CALCULATE (
    [Tonnage Movement],
    FILTER (
        ALLEXCEPT ( Data, Data[Stockpile_ID] ),
        'Data'[Date] &lt;= MAX ( 'Data'[Date] )
    )
)

Tonnage Movement =
CALCULATE ( SUMX ( 'Data', 'Data'[Tonnes] ), 'Data'[In_Code] = "IN" )
    - CALCULATE ( SUMX ( 'Data', 'Data'[Tonnes] ), 'Data'[In_Code] = "OUT" )This all works fine until there are intrastockpile movements - or movements between stockpiles. It shouldn't occur - but reality is of course at work.&nbsp;The problem is summarized in the following pictures:The issue occurs at the exclamation point ! above - in that Stockpile C has tonnes subtracted, Stockpile D has tonnes added, all in one transaction line in the database. My "In_Code" column isn't able to obviously handle both an "In" and "Out" code simulaneously.The above spreadsheet is an attempt to show in formulas what should&nbsp; happen to the tonnages.&nbsp;Does anyone have an idea to handle this elegantly in DAX? I have about 1.4M rows of transactions and I know I need to do some optimising on the calcs ( I think my SUMX iterators are possibly slowing things down) plus the fact the users want this information in a matrix table which is also adding to overheads. But first things first - how to handle the intra-stockpile movements? 🤷‍♂️😥Any help would be hugely appreciated. Been trying /struggling with USERELATIONSHIP but I was getting out of memory issues, and I am flat out of hair to pull out.&nbsp;Thanks in advanceManfred

roset03 · Answer

Hi Manfred,I also need to solve a similar problem, also for ore block to stockpile to crusher movements for an open pit operation... with an identical table.Just wondering if you managed to solve this one, and if you could kindly share some details!Many thanks,Tim

roset03 · Answer

Appreciate the update Manfred! Would be great if you could update us in due course. Hopefully we can compare notes, and work a really nice solution.

I assume your end goal is similar to mine - for blend planning for crusher feed grade?

Many thanks!

Tim

mwimberger · Answer

Hi&nbsp;roset03&nbsp;&nbsp;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.&nbsp;&nbsp;My solution was to do the following:Create a separate table data (filtered) of intrastockpile movements&nbsp;from the Data table&nbsp;Data Filtered IntraStockpile =
FILTER (
    Data,
    LEFT ( Data[Unified_Origen], 6 ) = "Stock "
        &amp;&amp; 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.&nbsp;Then I created a dimension table which generated a list of the&nbsp; 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] &lt;&gt; ""
)This dimension table was linked to the original data table and the filtered table - but it uses the following relationships:data[Source] *----&gt;tblStockpileIDUnique[Stockpile_ID_Detail] &lt;----* Data Filtered IntraStockpile[Destination]Tonnage In is calculated as&nbsp;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&nbsp;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] &lt; vLastDate )
    )

Closing Stock Tonnes = 
CALCULATE (
    [Tonnage Movement],
    FILTER (
        ALLEXCEPT ( Data, Data[Stockpile_ID], Data[Stockpile_ID_Detail] ),
        'Data'[FECHA] &lt;= 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.&nbsp; 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.&nbsp;IM me on LinkedIn if you need futher information - happy to discuss if you need any further assistance assistance.&nbsp;Cheers&nbsp;Manfred

Forum Discussion

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

5 Replies

Related content

Inventory and Stock balance calculation

Inventory Forecast

Inventory Management Dashboard

Inventory opening closing

Inventory Change from Beginning Value