Subtracting two rows

[KW123]

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 · Accepted solution]

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]

HI @KW123 
Please try the following

Result = 
VAR A = 1000
VAR B = 10
VAR CurrentDate = SELECTEDVALUE ( Data[Day] )
VAR CurrentYear = YEAR ( CurrentDate )
VAR CurrentMonth = MONTH ( CurrentDate )
VAR CurrentIndex = SELECTEDVALUE ( Data[INDEX] )
VAR MaxIndex = CALCULATE ( MAX ( Data[INDEX] ), YEAR ( Data[Day] ) = CurrentYear, MONTH ( Data[Day] ) = CurrentMonth, ALL ( Data ) )
RETURN
    IF ( 
        CurrentIndex <> BLANK ( ),
        A - ( MaxIndex - CurrentIndex ) * B
    )

[tamerj1]

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]

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

[tamerj1]

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

[KW123]

@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. 

[KW123]

@tamerj1 Does this help? 

Day	INDEX	What I am trying to calculate
1 Saturday	1	$810
2 Sunday
3 Monday (HOLIDAY)	1	$810
4 Tuesday	2	$820
5 Wednesday	3	$830
6 Thursday	4	$840
7 Friday	5	$850
8 Saturday	5	$850
9 Sunday
10 Monday	6	$860
11 Tuesday	7	$870
12 Wednesday	8	$880
13 Thursday	9	$890
14 Friday	10	$900
15 Saturday	10	$900
16 Sunday
17 Monday HOLIDAY	10	$900
18 Tuesday	11	$910
18 Wednesday	12	$920
20 Thursday	13	$930
21 Friday	14	$940
22 Saturday	14	$940
23 Sunday
24 Monday	15	$950
25 Tuesday	16	$960
26 Wednesday	17	$970
27 Thursday	18	$980
28 Friday	19	$990
29 Saturday	19	$990
30 Sunday
31 Monday	20	$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