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

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

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