Add and subtract from Running total

[Zidane22]

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

[tamerj1 · Accepted solution]

@Zidane22 

Try

Calculation =
VAR T =
    ALL ( 'All Web Site Data (2)' )
VAR T1 =
    TOPN ( 4, T, 'All Web Site Data (2)'[TIMES], ASC )
VAR StartTime =
    MAXX ( T1, 'All Web Site Data (2)'[TIMES] )
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] )
        ),
        IF (
            'All Web Site Data (2)'[TIMES] > StartTime,
            'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
        )
    )
RETURN
    InService - Result

[Zidane22]

Thank you @tamerj1 , You are awesome!

[Zidane22]

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.

 

[tamerj1]

@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.

[Zidane22]

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. 

[tamerj1]

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?

[Zidane22]

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 

[tamerj1]

@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

[Zidane22]

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 ?

[tamerj1]

@Zidane22 

"This is the one that starts at 90"

[Zidane22]

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  : ( 

[tamerj1]

@Zidane22 

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

[Zidane22]

@tamerj1  Please see last column, starting at 90 as an example.. (trucks left) the reults in the yelow column is what I'm looking for . for the one your talking about is the last column (TRUCKS LEFT) 

[tamerj1]

@Zidane22 

Why have you ignored the first 3 rows of [Trucks Available] column (6, 14 & 4)?

[Zidane22]

@tamerj1  , Sorry I i should have a starting time that everthing is defulted to zero to get the correct calculations. 

Lets start at 0300 ( no truck sign outs happens then)

Please see example how to calculate from the value 90 : 

[tamerj1]

@Zidane22 

This is exactly what my proosed dax is doing. To eliminate the problem of the first row please try

Calculation =
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] )
),
IF (
'All Web Site Data (2)'[Date] <> BLANK (),
'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
)
)
RETURN
InService - Result

[Zidane22]

@tamerj1 , this works, but I belive the caluclations crew up because of the times from 0000-0200. Anyway to start this from the Time 0300 ? I belive this would slove this issue.

Thank you so much again 

[tamerj1 · Accepted solution]

@Zidane22 

Try

Calculation =
VAR T =
    ALL ( 'All Web Site Data (2)' )
VAR T1 =
    TOPN ( 4, T, 'All Web Site Data (2)'[TIMES], ASC )
VAR StartTime =
    MAXX ( T1, 'All Web Site Data (2)'[TIMES] )
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] )
        ),
        IF (
            'All Web Site Data (2)'[TIMES] > StartTime,
            'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
        )
    )
RETURN
    InService - Result

[Zidane22]

Hello @tamerj1 !

Sorry to bother you again, 

If I were to use the value in the second row or thrid row etcc.. how can i modify ths part : 

VAR InService = MAXX ( T, 'All Web Site Data (2)'[EBS IN SERVICE] ) VAR Result = SUMX (

Thanks again!  

[tamerj1 · Accepted solution]

@Zidane22 

VAR InService =
MAXX (
TOPN (
1,
TOPN ( 3, T, 'All Web Site Data (2)'[TIMES], ASC ),
'All Web Site Data (2)'[TIMES]
),
'All Web Site Data (2)'[EBS IN SERVICE]
)

[Zidane22]

@tamerj1 , seems to be calculating correcly but the value is starting at Zero instead of the actual value, like the one we had before, (90) . Thanks again