cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Running Stockpile total

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?

1 ACCEPTED SOLUTION
Super User

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]
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Solution Sage

hi @bwjohnson ,

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
)

Super User

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]
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors