The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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]
)
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.
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]
)
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |