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

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

Zidane22 

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

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 : 

 

 

 

  • 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 

    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

  • 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?