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 · 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
    )

[KW123]

@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 

[tamerj1]

@KW123 

please provide sample of the expected results 

[KW123]

JANUARY 
VAR A= $1000 
VAR B= $10 

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)

FEBRUARY
VAR A=$2500
VAR B= $50

Day	INDEX	What I am trying to calculate
1 Tuesday	1	$650
2 Wednesday	2	$700
3 Thursday	3	$750
4 Friday	4	$800
5 Saturday	4	$800
6 Sunday
7 Monday	5	$850
8 Tuesday	6	$900
9 Wednesday	7	$950
10 Thursday	8	$1000
11 Friday	9	$1050
12 Saturday	9	$1050
13 Sunday
14 Monday	10	$2000
15 Tuesday	11	$2050
16 Wednesday	12	$2100
17 Thursday	13	$2150
18 Friday	14	$2200
18 Saturday	14	$2200
20 Sunday
21 Monday HOLIDAY	14	$2200
22 Tuesday	15	$2250
23 Wednesday	16	$2300
24 Thursday	17	$2350
25 Friday	18	$2400
26 Saturday	18	$2450
27 Sunday
28 Monday	19	$2500

In my report, instead of starting at "$650" it's starting at "$50" Does that make sense? 

[tamerj1]

@KW123 

yes all clear. Best approach is create a year month number column. I assume you have a date column so the new column would be 

YEAR ( Data[Date] ) * 100 + MONTH ( Data[Date] )

then use this column inside ALLEXCEPT instead of Data[Date].[Month]

[KW123]

@tamerj1 
I think I found the issue.  The [Accounting goal calculation] is a column imported direct from excel.  I wasn't able to plug it into the formula so I created a measure for it (see screen shot) but it doesn't appear to show any numbers.  This could be why the Dax you gave me isn't working? 

[tamerj1]

@KW123 
I think we need to connect to clarify the pivture. Please let me know when we can do that

[KW123]

@tamerj1 I got it!!! I had to slightly adjust the DAX but it's working.  

My only other question is that the January 1 (Saturday) and January 3 (Monday) have the same Index number which needs to stay the same.  Is there a way however to have the value we just calculated for January 1 be $0? Since it's the first day of the year there should be no values.  But of course Monday has a value since we had sales that day.  Maybe an IF clause???

[tamerj1]

@KW123 
Yes just add IF ( DAY ( SELECTEDVALUE ( Data[Day] ) = 1, 0, IF ( CurrentIndex <> BLANK ( ), A - ( MaxIndex - CurrentIndex ) * B ) )

[KW123]

[tamerj1]

@KW123 
Delete the closing bracket after the 0

[KW123]

[tamerj1]

@KW123 
Please copy paste the code in a reply so I can edit it for you.

[KW123]

@tamerj1 

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, 0,
IF ( CurrentIndex <> BLANK ( ), A + ( MaxIndex - CurrentIndex ) * B )

[tamerj1]

@KW123 

Here you go

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,
        0,
        IF ( CurrentIndex <> BLANK (), A + ( MaxIndex - CurrentIndex ) * B )
    )

[KW123]

@tamerj1 This works thank you!!! Now it's making each first of each month $0.  I just need it to make January 1 = $0

[tamerj1 · Accepted solution]

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

@tamerj1 
That was it!! Thank you so so much for all of your help, I really appreciate it. 

[KW123]

[KW123]

@tamerj1 
Thank you! It still doesn't seem to be returning the correct values......