topic Re: Add and subtract from Running total in DAX Commands and Tips

Add and subtract from Running total

Zidane22 — Tue, 04 Apr 2023 15:18:43 GMT

Hello everyone,

Using the foruma below I get the running total, which is great but I dont want it to keep adding to itself if there is no value (column1) to be added. I also need it to subtract (column2) if there is a value.

For example :

Column 1. Column 2 Measure I need displayed:

0. - 0 0

7 - 0 7

26 - 5 28 -- ( 7+ 26 = 33 -5 =28)

0. - 3 25 ( 28+0 = 28 -3 =25)

Any help to achive this would mean so much, I've been suck on this for days!

Running Total MEASURE = CALCULATE ( SUM ( 'All Web Site Data (2)'[UniquePageviews] ), FILTER ( ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] ) ) )

Re: Add and subtract from Running total

tamerj1 — Tue, 04 Apr 2023 15:34:32 GMT

Hi Zidane22

please try

RunningTotal =
SUMX (
FILTER (
ALL ( 'All Web Site Data (2)' ),
'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
),
'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
)

perhaps Greg_Deckler can provide you with a solution based on CALCULATE 😜

Re: Add and subtract from Running total

Zidane22 — Tue, 04 Apr 2023 20:30:51 GMT

Thank you tamerj1 ,

It looks like its working I just have to check the math.

Is their away to start the value of this calcuation of a from 0? have a differnt calculation that starts with the differnt column first value? So if the value is 90 in the in first item then it would start at 90 then go backwords into the negtives when it passes 1. 2 two measures . One starts a 0 (this one) and second one starts with the first value in a differnt column (90) for example. so I would have two lines

This calculation is used in line graph give the total after the light purple - oranage, give its total and carrying that total to the next line. Hope that makes sense.

Re: Add and subtract from Running total

tamerj1 — Tue, 04 Apr 2023 20:43:35 GMT

Zidane22

Please provide a sample along with at least one example

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 03:59:15 GMT

Hello tamerj1 ,

Please see exampe below. What i need t calcualute is the two columns in the yellow in two differnt measures.

Thank you so much for your help, really means alot. I've been really strugling with this.

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 04:14:27 GMT

Zidane22
Taken from another column in the same table? Is it the sum of that column or the first value or what exactly? I noticed that sonetimes you are subtracting the TRUCKS AVILABLE and sometimes not, please clarify.

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 13:04:58 GMT

Sorry Im not being clear, Its the same Table but differnt column name "EBS IN SERVICE" the first item will have the value that i need not the sum. Yes only subtracting if trucks are in use. These two calcuations basically need to tell me two things. 1) how many trucks are being used per hour (which you get from "TRUCKS REQ" subtracting "TRUCKS AVAILABLE" which is trucks coming back being retuned. 2) I only have certian amount of trucks in service, which the value dose change automaticaly and this is were the (90) for example comes in. Bascially in this calculation it will forcast if we will have enough trucks by mid day or we would be short beause its subtracting the amount of trucks that we need from what's actually in service. Hope this makes it more clear. Thank you again so much.

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 16:30:14 GMT

Thank you Zidane22 for this explanation and sorry for asking too many questions.
"the first item will have" is this when TIMES = 0?

"only subtracting if trucks are in use" would you please elaborate on this?

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 16:58:29 GMT

No problem, I know your asking so you can help me correctly. Really means alot.

Yes, it will be in the same row in "000" but in a diffrent column.

So its like a car rental company. I need to know how many Cars are needed per hour based on cutsomer reservations. so if I have 10 customers at 0100, I need 10 Cars. if at 0200 I have another 10 customers that need 10 cars then the requirment by 0200 is 20 cars. But if at 0200 I have 5 customers that return thier cars then I actually only need 15 cars now because someone who had the cars previoulsy have returned it so the total 20 cars are not needed anymore. It only subtracts when TRUCKS are availble (I should actual name that column retuned sorry) - hope this makes more sense

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 19:04:36 GMT

Zidane22

Please try

RunningTotal =
VAR T =
ALL ( 'All Web Site Data (2)' )
VAR FirstRecord =
TOPN ( 1, T, 'All Web Site Data (2)'[Date], ASC )
VAR InService =
MAXX ( FirstRecod, 'All Web Site Data (2)'[EBS IN SERVICE] )
VAR Result =
SUMX (
FILTER (
T,
'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
),
'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
)
RETURN
InService + Result

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 19:51:31 GMT

Thank you so much, I will try now. Is that one measure for both? Wouldn't i need two sepreate calculations? One for each yellow column ?

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 19:56:05 GMT

Zidane22

"This is the one that starts at 90"

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 20:32:20 GMT

So it seems its calculating the same way your first forumla provided. It's not starting from the first value, which is currently 90, it's started from -4 : (

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 20:54:53 GMT

Zidane22

Can you share a screenshot showing the column that contains the starting value?

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 21:24:30 GMT

tamerj1

Re: Add and subtract from Running total

tamerj1 — Wed, 05 Apr 2023 21:43:24 GMT

Zidane22

Please try

RunningTotal =
VAR T =
ALL ( 'All Web Site Data (2)' )
VAR InService =
MAXX ( T, 'All Web Site Data (2)'[EBS IN SERVICE] )
VAR Result =
SUMX (
FILTER (
T,
'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
),
'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
)
RETURN
InService + Result

Re: Add and subtract from Running total

Zidane22 — Wed, 05 Apr 2023 21:53:56 GMT

Not sure.. it jumps to the 100's which dosen't make sense, should be counting down as they day goes on not counting up...

Re: Add and subtract from Running total

tamerj1 — Thu, 06 Apr 2023 03:31:15 GMT

Zidane22
Noted. Please try

RunningTotal = VAR T = ALL ( 'All Web Site Data (2)' ) VAR InService = MAXX ( T, 'All Web Site Data (2)'[EBS IN SERVICE] ) VAR Result = SUMX ( FILTER ( T, 'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] ) ), 'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2] ) RETURN InService - Result

Re: Add and subtract from Running total

Zidane22 — Thu, 06 Apr 2023 14:09:17 GMT

Hello tamerj1 ,

it now going down but still jumping to 100 which dose not make sense when the max number is 90. I will play around with it .

Can you please help me with the meausue for the first column please? Trucks being used? Thank you again for all the help.

Re: Add and subtract from Running total

tamerj1 — Thu, 06 Apr 2023 15:26:52 GMT

Zidane22

Do you mean without the 90? What is the issue with my first dax?