find the rolling difference between 2 different (not earlier one) rows on the same column.

[Anonymous]

I need to find the difference of values of the current week and the value of 13 months apart in power bi.  

Subtracting the current order quantity Value and the value 13 year before value (and create a running subtraction) based on the calendar.  

Calendar year	Order quantity
5/30/2022	478
5/23/2022	511
5/16/2022	58
5/9/2022	194
5/2/2022	61
4/25/2022	81
4/18/2022	203
4/11/2022	125
4/4/2022	56
3/28/2022	55
3/28/2022	80
3/21/2022	85
3/14/2022	165
3/7/2022	90
2/28/2022	21
2/28/2022	4
2/21/2022	92
2/14/2022	110
2/7/2022	51
1/31/2022	73
1/31/2022	49
1/24/2022	72
1/17/2022	86
1/10/2022	67
1/3/2022	62
12/27/2021	633
12/20/2021	138
12/13/2021	60
12/6/2021	69
11/29/2021	60
11/29/2021	104
11/22/2021	519
11/15/2021	124
11/8/2021	139
11/1/2021	164
10/25/2021	31
10/18/2021	149
10/11/2021	113
10/4/2021	95
9/27/2021	8
9/27/2021	22
9/20/2021	212
9/13/2021	565
9/6/2021	194
8/30/2021	27
8/30/2021	619
8/23/2021	88
8/16/2021	80
8/9/2021	79
8/2/2021	552
7/26/2021	77
7/19/2021	80
7/12/2021	19
7/5/2021	69
6/28/2021	27
6/28/2021	58
6/21/2021	57
6/14/2021	231
6/7/2021	482
5/31/2021	41
5/31/2021	29
5/24/2021	56
5/17/2021	23
5/10/2021	254
5/3/2021	44
4/26/2021	36
4/19/2021	770
4/12/2021	92
4/5/2021	28
3/29/2021	28
3/29/2021	142
3/22/2021	70
3/15/2021	121
3/8/2021	219
3/1/2021	120
2/22/2021	38
2/15/2021	104
2/8/2021	211
2/1/2021	108
1/25/2021	392
1/18/2021	453
1/11/2021	39
12/21/2020	117
12/14/2020	170
12/7/2020	225
11/30/2020	84
11/30/2020	16
11/23/2020	217
11/16/2020	99
11/9/2020	61
11/2/2020	25
10/26/2020	138
10/19/2020	427
10/12/2020	38
10/5/2020	162
9/28/2020	132
9/28/2020	125
9/21/2020	288
9/14/2020	51
9/7/2020	58
8/31/2020	63
8/31/2020	107
8/24/2020	248
8/17/2020	71
8/10/2020	743
8/3/2020	111
7/27/2020	387
7/20/2020	106
7/13/2020	54
7/6/2020	333
6/29/2020	255
6/29/2020	22
6/22/2020	13
6/15/2020	324
6/8/2020	196
6/1/2020	50
5/25/2020	255
5/18/2020	85
5/11/2020	142
5/4/2020	102

The result should look like this: 

subtracting orderquantity colum in different rows :

478(b2)- 81(b26)= 416  

511(b3)-633(b27) =-122

[v-cgao-msft]

Hi @Anonymous ,

//The result should look like this: 

subtracting orderquantity colum in different rows :

478(b2)- 81(b26)= 416  

511(b3)-633(b27) =-122

It seems that the expected output is not calculated according to the rules described above, would you mind explaining again how it is calculated or checking the expected output for a typo? Thanks in advance.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Here is what the result should look like in excel-  @v-cgao-msft @amitchandak . Please ignore the earlier resluts. 

the formula for the Subtract colum in excel is

Row 1= C2 - C54

Row 2= C3 - C55
Row 3= C4 - C56

Calendar year	Order quantity	Subtract
5/30/2022	478	459
5/23/2022	511	442
5/16/2022	58	31
5/9/2022	194	136
5/2/2022	61	4
4/25/2022	81	-150
4/18/2022	203	-279
4/11/2022	125	84
4/4/2022	56	27
3/28/2022	55	-1
3/28/2022	80	57
3/21/2022	85	-169
3/14/2022	165	121
3/7/2022	90	54
2/28/2022	21	-749
2/28/2022	4	-88
2/21/2022	92	64
2/14/2022	110	82
2/7/2022	51	-91
1/31/2022	73	3
1/31/2022	49	-72
1/24/2022	72	-147
1/17/2022	86	-34
1/10/2022	67	29
1/3/2022	62	-42
12/27/2021	633	422
12/20/2021	138	30
12/13/2021	60	-332
12/6/2021	69	-384
11/29/2021	60	21
11/29/2021	104	-13
11/22/2021	519	349
11/15/2021	124	-101
11/8/2021	139	55
11/1/2021	164	148
10/25/2021	31	-186
10/18/2021	149	50
10/11/2021	113	52
10/4/2021	95	70
9/27/2021	8	-130
9/27/2021	22	-405
9/20/2021	212	174
9/13/2021	565	403
9/6/2021	194	62
8/30/2021	27	-98
8/30/2021	619	331
8/23/2021	88	37
8/16/2021	80	22
8/9/2021	79	16
8/2/2021	552	445
7/26/2021	77	-171
7/19/2021	80	9
7/12/2021	19	-724
7/5/2021	69	-42
6/28/2021	27	-360
6/28/2021	58	-48
6/21/2021	57	3
6/14/2021	231	-102
6/7/2021	482	227
5/31/2021	41	19
5/31/2021	29	16
5/24/2021	56	-268
5/17/2021	23	-173
5/10/2021	254	204
5/3/2021	44	-211
4/26/2021	36	-49
4/19/2021	770	628
4/12/2021	92	-10
4/5/2021	28	28
3/29/2021	28	28
3/29/2021	142	142
3/22/2021	70	70
3/15/2021	121	121
3/8/2021	219	219
3/1/2021	120	120
2/22/2021	38	38
2/15/2021	104	104
2/8/2021	211	211
2/1/2021	108	108
1/25/2021	392	392
1/18/2021	453	453
1/11/2021	39	39
12/21/2020	117	117
12/14/2020	170	170
12/7/2020	225	225
11/30/2020	84	84
11/30/2020	16	16
11/23/2020	217	217
11/16/2020	99	99
11/9/2020	61	61
11/2/2020	25	25
10/26/2020	138	138
10/19/2020	427	427
10/12/2020	38	38
10/5/2020	162	162
9/28/2020	132	132
9/28/2020	125	125
9/21/2020	288	288
9/14/2020	51	51
9/7/2020	58	58
8/31/2020	63	63
8/31/2020	107	107
8/24/2020	248	248
8/17/2020	71	71
8/10/2020	743	743
8/3/2020	111	111
7/27/2020	387	387
7/20/2020	106	106
7/13/2020	54	54
7/6/2020	333	333
6/29/2020	255	255
6/29/2020	22	22
6/22/2020	13	13
6/15/2020	324	324
6/8/2020	196	196
6/1/2020	50	50
5/25/2020	255	255
5/18/2020	85	85
5/11/2020	142	142
5/4/2020	102	102

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

You can add a new index column to the PowerQuery Eidtor.

Then create a calculated column.

Column = 
VAR _order = 'Table'[Order quantity]
VAR _index_subtract = 'Table'[Index] + 52
VAR _subtract =
    CALCULATE (
        MAX ( 'Table'[Subtract] ),
        FILTER ( 'Table', 'Table'[Index] = _index_subtract )
    )
VAR _result = _order - _subtract
RETURN
    _result

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Hi sorry, for the late reply, I did not see this on time.

I wanted to subtract values from different rows in same column. I am subtracting order quantity by date by 52 weeks apart. And please dont do it on 13 months. Do it on weekly basis, which is 52 weeks.

Max Date is the first one and you will subtract it  with a value 52 weeks later value.

I will update the question. Please Thanks in advance. Let me know if you need something else.  

[amitchandak]

@Anonymous , what I can see is 24-week part.

With help from week Rank, Prefer a separate week or date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

24 the Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-24))