Forum Discussion

KW123's avatar
KW123
Icon for Helper V rankHelper 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 t...
  • 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 )
    )
KW123's avatar
KW123
Icon for Helper V rankHelper 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
Icon for Community Champion rankCommunity 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.

  • KW123's avatar
    KW123
    Icon for Helper V rankHelper V
    4 years ago

    tamerj1 Does this help? 

    DayINDEXWhat I am trying to calculate
    1 Saturday1$810
    2 Sunday  
    3 Monday (HOLIDAY)1$810
    4 Tuesday2$820
    5 Wednesday3$830
    6 Thursday4$840
    7 Friday5$850
    8 Saturday5$850
    9 Sunday  
    10 Monday6$860
    11 Tuesday7$870
    12 Wednesday8$880
    13 Thursday9$890
    14 Friday10$900
    15 Saturday10$900
    16 Sunday  
    17 Monday HOLIDAY 10$900
    18 Tuesday11$910
    18 Wednesday12$920
    20 Thursday13$930
    21 Friday14$940
    22 Saturday14$940
    23 Sunday  
    24 Monday15$950
    25 Tuesday16$960
    26 Wednesday17$970
    27 Thursday18$980
    28 Friday19$990
    29 Saturday19$990
    30 Sunday  
    31 Monday20$1000 (I only know this number, I need to work in reverse from here)


    The first value I am subtracting from is the "$1000" and I will move up the month backwards.  Assume the "B" value is $10 so we will need to subtract the $10 from every new row.  The index column is based on filters on days of the week/holidays 

  • KW123's avatar
    KW123
    Icon for Helper V rankHelper V
    4 years ago

    tamerj1 
    The $1000 example changes each month. The data is in a table with 12 rows, one for each month and is linked to a calendar table with a month relationship. The $10 example is a measure and is the same for the entire month but also changes each month.