Forum Discussion

Zidane22's avatar
Zidane22
Icon for Helper II rankHelper II
3 years ago
Solved

Add and subtract from Running total

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 ...
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
Zidane22
Icon for Helper II rankHelper II
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    All good now thank you again.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Zidane22 

    Adjust the "3" to obtain the correct value 

  • Zidane22's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    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 

  • Zidane22's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    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!  

  • Zidane22's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    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 

  • Zidane22's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    tamerj1 , sorry for the stupid question , I Did put three and still shows Zero,  where do i put the three exactly?