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

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

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

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

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

        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 ?