Forum Discussion

KW123's avatar
KW123
Helper V
4 years ago
Solved

Subtracting two rows

Hi, 

I am hoping someone can help.  I have a column with sales values and I need to create a DAX where I minus A from B to get C but then take C and minus it from B to get D.  Then D-B=E etc for the entire month. I have a dates table and an index. 

A-B=C
C-B=D
D-B=E
E-B=F
etc

Is there a way to do this? 

Thanks, 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi KW123 
    Sorry it was too late yesterday I couldn't reply to you. Here is a sample file with the solution jnowing that you will retun back to me with more information that you've been hiding as usual ðŸ˜‰https://www.dropbox.com/t/lzKxh0DxLA056sy7

    Result = 
VAR A = 1000
VAR B = 10
VAR CurrentIndex = SELECTEDVALUE ( Data[INDEX] )
VAR MaxIndex = CALCULATE ( MAX ( Data[INDEX] ), ALLEXCEPT ( Data, Data[Day].[Month] ) )
RETURN
    IF ( 
        CurrentIndex <> BLANK ( ),
        A - ( MaxIndex - CurrentIndex ) * B
    )



  • tamerj1's avatar
    tamerj1
    4 years ago

    KW123 

    What does YTD total need to be =
VAR A = [Accounting goal calc-c]
VAR B = [Daily Goal]
VAR CurrentIndex =
    SELECTEDVALUE ( Dates[FD2] )
VAR MaxIndex =
    CALCULATE ( MIN ( Dates[FD2] ), ALLEXCEPT ( Dates, Dates[Date] ) )
RETURN
    IF (
        DAY ( SELECTEDVALUE ( Dates[Day] ) ) = 1
            && MONTH ( SELECTEDVALUE ( Dates[Day] ) ) = 1,
        0,
        IF ( CurrentIndex <> BLANK (), A + ( MaxIndex - CurrentIndex ) * B )
    )

26 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi KW123 
    C = A - B
    D = A - 2 * B
    E = A - 3 * B
    F = A - 4 * B

    This is half the solution answering your half question. 
    Does that help?

    • KW123's avatar
      KW123
      Helper V
      4 years ago

      tamerj1 Yes this could work, I just don't know how to turn that into a measure or a calculated column 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        KW123 
        I think this is the time were you have to disclose some sample data so I can complete my answer.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi KW123 
    Sorry it was too late yesterday I couldn't reply to you. Here is a sample file with the solution jnowing that you will retun back to me with more information that you've been hiding as usual ðŸ˜‰https://www.dropbox.com/t/lzKxh0DxLA056sy7

    Result = 
VAR A = 1000
VAR B = 10
VAR CurrentIndex = SELECTEDVALUE ( Data[INDEX] )
VAR MaxIndex = CALCULATE ( MAX ( Data[INDEX] ), ALLEXCEPT ( Data, Data[Day].[Month] ) )
RETURN
    IF ( 
        CurrentIndex <> BLANK ( ),
        A - ( MaxIndex - CurrentIndex ) * B
    )



    • KW123's avatar
      KW123
      Helper V
      4 years ago

      tamerj1 
      Thank you very much for your help! I think this is what I am looking for.  The thing is, when I change the month, it isn't starting from the months goal ($1000 in the example case) How would we get it to start at the current months goal? It looks as though February is starting at the VAR B amount for that month