Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks
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 source and to destinations for an open pit operation. In most cases, the opening and closing balances are solved using the following measures:
Opening Stock Tonnes = CALCULATE ( [Tonnage Movement], FILTER ( ALLEXCEPT ( Data, Data[Stockpile_ID],Data[Stockpile_ID_Detail] ) ,'Data'[Date] < min('Data'[Date]) ) Closing Stock Tonnes = CALCULATE ( [Tonnage Movement], FILTER ( ALLEXCEPT ( Data, Data[Stockpile_ID] ), 'Data'[Date] <= 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.
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 happen to the tonnages.
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.
Thanks in advance
Manfred
Hi Tim
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) and a relationship of this query to the Distinct list of locations ( in and outs)
I will detail exactly how I did this with a longer post soon. Good to see some mining related interest out there!
Cheers
Manfred
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
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
Hi Folks
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 source and to destinations for an open pit operation. In most cases, the opening and closing balances are solved using the following measures:
Opening Stock Tonnes = CALCULATE ( [Tonnage Movement], FILTER ( ALLEXCEPT ( Data, Data[Stockpile_ID],Data[Stockpile_ID_Detail] ) ,'Data'[Date] < min('Data'[Date]) ) Closing Stock Tonnes = CALCULATE ( [Tonnage Movement], FILTER ( ALLEXCEPT ( Data, Data[Stockpile_ID] ), 'Data'[Date] <= 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.
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 happen to the tonnages.
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.
Thanks in advance
Manfred
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |