topic Re: Subtracting two rows in DAX Commands and Tips

Subtracting two rows

KW123 — Mon, 01 Aug 2022 17:52:27 GMT

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,

Re: Subtracting two rows

tamerj1 — Mon, 01 Aug 2022 18:18:17 GMT

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?

Re: Subtracting two rows

KW123 — Mon, 01 Aug 2022 18:21:32 GMT

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

Re: Subtracting two rows

tamerj1 — Mon, 01 Aug 2022 18:23:56 GMT

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

Re: Subtracting two rows

KW123 — Mon, 01 Aug 2022 18:29:17 GMT

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

Re: Subtracting two rows

KW123 — Mon, 01 Aug 2022 18:33:57 GMT

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.

Re: Subtracting two rows

tamerj1 — Tue, 02 Aug 2022 13:56:08 GMT

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 )

Re: Subtracting two rows

KW123 — Tue, 02 Aug 2022 19:49:05 GMT

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

Re: Subtracting two rows

tamerj1 — Tue, 02 Aug 2022 20:06:38 GMT

KW123

please provide sample of the expected results

Re: Subtracting two rows

KW123 — Tue, 02 Aug 2022 20:19:18 GMT

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?

Re: Subtracting two rows

tamerj1 — Tue, 02 Aug 2022 21:24:51 GMT

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]

Re: Subtracting two rows

KW123 — Tue, 02 Aug 2022 22:12:16 GMT

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

Re: Subtracting two rows

KW123 — Tue, 02 Aug 2022 22:12:59 GMT

Re: Subtracting two rows

KW123 — Tue, 02 Aug 2022 22:58:44 GMT

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?

Re: Subtracting two rows

tamerj1 — Wed, 03 Aug 2022 04:36:35 GMT

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 )

Re: Subtracting two rows

tamerj1 — Wed, 03 Aug 2022 04:41:26 GMT

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

Re: Subtracting two rows

KW123 — Wed, 03 Aug 2022 18:35:51 GMT

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

Re: Subtracting two rows

tamerj1 — Wed, 03 Aug 2022 18:39:41 GMT

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

Re: Subtracting two rows

KW123 — Wed, 03 Aug 2022 18:53:57 GMT

Re: Subtracting two rows

tamerj1 — Wed, 03 Aug 2022 18:55:07 GMT

KW123
Delete the closing bracket after the 0