Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Difference between 2 different rows in same column (but not the earlier row) [only with dax]

I need the difference of weekly orders between 52 weeks (in this case it is 52 rows) for weekly orders. 

Adjusted Value is the result I am looking for. 

The formula in excel i used is :

**Subtraction from row 2 minus row 54 [for weekly orders to find the diff between 52 weeks(52 rows)]

**=b2-b54 [//(weekly orders)]

= 35897(row 2, b2) - 25797 (row 54, b54)

 

WEEK WEEKLY ORDERS  Results>>>> DIFF VALUE
10/19/2020                35,897   10100.00
10/12/2020                35,786   10458.00
10/5/2020                  6,996   -21319.00
9/28/2020                21,720   -581.00
9/21/2020                30,439   -9415.00
9/14/2020                17,325   -4367.00
9/7/2020                11,699   -8067.00
8/31/2020                46,739   38268.00
8/24/2020                27,335   12311.00
8/17/2020                52,396   21604.00
8/10/2020                27,231   1655.00
8/3/2020                19,000   -15555.00
7/27/2020                28,192   11199.00
7/20/2020                19,555   6218.00
7/13/2020                17,449   1080.00
7/6/2020                13,884   10601.00
6/29/2020                43,167   43097.00
6/22/2020                27,131   -155630.00
6/15/2020                16,318   -29918.00
6/8/2020                27,475   -573.00
6/1/2020                  7,437   -1880.00
5/25/2020                  5,469   -49614.00
5/18/2020                  9,248   -10109.00
5/11/2020                21,918   13994.00
5/4/2020                44,218   28886.00
4/27/2020                19,306   8411.00
4/20/2020                  3,145   -14919.00
4/13/2020                15,925   -9578.00
4/6/2020                  3,738   -33354.00
3/30/2020                13,842   6818.00
3/23/2020                21,414   -18517.00
3/16/2020                11,580   -17013.00
3/9/2020                17,919   -19687.00
3/2/2020                13,916   -25172.00
2/24/2020                28,384   -16994.00
2/17/2020                24,706   7396.00
2/10/2020                45,350   -14620.00
2/3/2020                73,480   28072.00
1/27/2020                53,978   37028.00
1/20/2020                13,476   2958.00
1/13/2020                14,676   -8886.00
1/6/2020                  9,403   -5051.00
12/30/2019                11,057   -38869.00
12/23/2019                20,286   -8376.00
12/16/2019                21,449   3869.00
12/9/2019                20,765   2909.00
12/2/2019                21,071   -35089.00
11/25/2019                24,723   -5823.00
11/18/2019                26,971   13537.00
11/11/2019                20,878   -17834.00
11/4/2019                66,940   52828.00
10/28/2019                  7,108    
10/21/2019                25,797    
10/14/2019                25,328    
10/7/2019                28,315    
9/30/2019                22,301    
9/23/2019                39,854    
9/16/2019                21,692    
9/9/2019                19,766    
9/2/2019                  8,471    
8/26/2019                15,024    
8/19/2019                30,792    
8/12/2019                25,576    
8/5/2019                34,555    
7/29/2019                16,993    
7/22/2019                13,337    
7/15/2019                16,369    
7/8/2019                  3,283    
7/1/2019                       70    
6/24/2019              182,761    
6/17/2019                46,236    
6/10/2019                28,048    
6/3/2019                  9,317    
5/27/2019                55,083    
5/20/2019                19,357    
5/13/2019                  7,924    
5/6/2019                15,332    
4/29/2019                10,895    
4/22/2019                18,064    
4/15/2019                25,503    
4/8/2019                37,092    
4/1/2019                  7,024    
3/25/2019                39,931    
3/18/2019                28,593    
3/11/2019                37,606    
3/4/2019                39,088    
2/25/2019                45,378    
2/18/2019                17,310    
2/11/2019                59,970    
2/4/2019                45,408    
1/28/2019                16,950    
1/21/2019                10,518    
1/14/2019                23,562    
1/7/2019                14,454    
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please try this:

    DIFF VALUE 2 = 
VAR _date = 'Table'[WEEK] - 364
VAR _value =
    'Table'[WEEKLY ORDERS]
        - CALCULATE (
            SUM ( 'Table'[WEEKLY ORDERS] ),
            FILTER ( 'Table', 'Table'[WEEK] = _date )
        )
RETURN
    _value

    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

3 Replies