topic Re: Running Stockpile total in DAX Commands and Tips

Running Stockpile total

Anonymous — Thu, 01 Feb 2024 17:07:42 GMT

I am trying to get a stockpile balance using Power BI. I have 4 columns in my sample table with this data. I would like to add a column that will show me the current stockpile balance. This running total needs to start over whenever a new entry is made in the STOCKPILESURVEYTONS column. So in the below data, my balance would be 408922 for every day from 1/1/24 thru 1/27/24. On 1/27 the balance should be 400000 (new survey) then on 1/29 it should be 400500 (added 500 tons to pile) and on 1/30 the balance should be 400000 (removed 500 tons from pile). How can I write that in DAX?

Re: Running Stockpile total

Jihwan_Kim — Fri, 02 Feb 2024 04:54:21 GMT

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

It is for creating a new calculated column.

expected result CC = VAR _lastnonblankdate = MAXX ( FILTER ( Data, Data[date] <= EARLIER ( Data[date] ) && Data[stockpilesurveytons] <> BLANK () ), Data[date] ) RETURN SUMX ( FILTER ( Data, Data[date] = _lastnonblankdate ), Data[stockpilesurveytons] ) + SUMX ( FILTER ( Data, Data[date] >= _lastnonblankdate && Data[date] <= EARLIER ( Data[date] ) ), Data[tonstostockpile] ) - SUMX ( FILTER ( Data, Data[date] >= _lastnonblankdate && Data[date] <= EARLIER ( Data[date] ) ), Data[tonsfromstockpile] )

Re: Running Stockpile total

talespin — Fri, 02 Feb 2024 11:13:20 GMT

hi Anonymous ,

Sharing another method to do it

Logic Used

First get the max survey date for each row which is less than the current row date.

Calculate SUM for date >= date calculated above and <= current row date.

Column =

VAR _CurrRowDate = TestTbl2[Dt]

VAR _LastSurveyDate = CALCULATE( MAX(TestTbl2[Dt]), REMOVEFILTERS(TestTbl2), TestTbl2[Dt] <= _CurrRowDate, TestTbl2[StockpileSurveyTons] <> BLANK())

RETURN CALCULATE(

SUMX( TestTbl2,

TestTbl2[StockpileSurveyTons] + TestTbl2[TonsToStockpile] - TestTbl2[TonsFromStockpile]

REMOVEFILTERS(TestTbl2),

TestTbl2[Dt] >= _LastSurveyDate

&& TestTbl2[Dt] <= _CurrRowDate

)