Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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 |
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
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.
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |