Forum Discussion
AgataJ
Helper II
Helper IISubtracting Running Total from first available value in a table
Good morning, I am working on such calculations: I have a column with Hours and Hours Used. For Hours used I need to calculate Running Total, what I have a DAX for it no problem. My problem starts...
Try this
Answer = // get first date var myfirstdate = CALCULATE( MIN(yourdata[Date]), ALl(yourdata) ) // get hours for first date var myfirstvalue = CALCULATE( SUM(yourdata[Hours]), ALL(yourdata), yourdata[Date] <= myfirstdate) // get date for current row in visual var mydate = SELECTEDVALUE(yourdata[Date]) // get running total for row var runningtotal = CALCULATE( SUM(yourdata[Hours used]), ALL(yourdata), yourdata[Date] <= mydate) RETURN // deduct running toral from first value myfirstvalue - runningtotalPlease click the [accept solution] and thumbs up buttons. Thank you
speedramps
Super User
Super UserTry this
Answer =
// get first date
var myfirstdate =
CALCULATE(
MIN(yourdata[Date]),
ALl(yourdata)
)
// get hours for first date
var myfirstvalue =
CALCULATE(
SUM(yourdata[Hours]),
ALL(yourdata),
yourdata[Date] <= myfirstdate)
// get date for current row in visual
var mydate = SELECTEDVALUE(yourdata[Date])
// get running total for row
var runningtotal =
CALCULATE(
SUM(yourdata[Hours used]),
ALL(yourdata),
yourdata[Date] <= mydate)
RETURN
// deduct running toral from first value
myfirstvalue - runningtotal
Please click the [accept solution] and thumbs up buttons. Thank you
- AgataJ
Hi speedramps ,
My bad, I focused on the project and forgot to asnwer. Your code works well, thanks a million!
I had to filter it to start the calculation from the first date when Cum_Complete is not blank. I did it using this code:
Answer2 filtered = CALCULATE([Answer 2], 'MRP History'[Complete]<>0). It works properly however still shows the first line when cum_complete is empty, would you know maybe how to show the first line with cum_complete not blank (in the example case it'd be 02-Jan_2025)? I cannot filter it by cum_complete >0 as I would like to see other empty lines , like here:Thanks in advance,
Agata