cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSS
Helper I
Helper I

Difference between specified rows

Good Afternoon,

 

I have been struggling with the following issue, I do not know if it's possible to do it in PowerBI/DAX.

 

My data looks like:

 

Date               Continent          Country        Company          Operations
01/01/2017    Europe              Spain             Google               500
01/01/2017    Europe              Spain             Apple                 250
01/01/2017    Europe              France           Google               300
01/01/2017    Europe              France           Microsoft            400
01/01/2017    Europe              Italy               Google               100
02/01/2017    Europe              Spain             Google               300
02/01/2017    Europe              Spain             Apple                 100
.
.
.

And I would like to add a column that correponds to the difference between consecutive days for the same company in the same country (for example, 500 (Google/Spain/01.01.2017) - 300 (Google/Spain/02.01.2017) = 200) and this 200 should be positioned in the first row of the next column (in our example it would look like)

 

Date               Continent          Country        Company          Operations      Difference
01/01/2017    Europe              Spain             Google               500                  200
01/01/2017    Europe              Spain             Apple                 250                  150 (250-100)
01/01/2017    Europe              France           Google               300                   .
01/01/2017    Europe              France           Microsoft            400                   .
01/01/2017    Europe              Italy               Google               100                   .
02/01/2017    Europe              Spain             Google               300
02/01/2017    Europe              Spain             Apple                 100

 

Is this possible?

 

The formula that I have tried is the following:

[Difference] = CALCULATE (
    SUM( 'Sheet1'[Operations] );
    FILTER(ALL('Sheet1' );
    'Sheet1'[Date] <= EARLIER ('Sheet1'[Date] )
    && 'Sheet1'[Country] = EARLIER( ('Sheet1'[Country]) )
    && 'Sheet1'[Company] = EARLIER('Sheet1'[Company])
))

 

But I am only able to sum the values between the different company/country/consecutive dates, not being able to perform the difference.

 

 

I would be really grateful if someone can help me.

 

Thanks!!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@SSS

 

Hi. Try this calculated column

 

=
VAR FollowingDay =
    NEXTDAY ( Sheet1[Date] )
RETURN
    Sheet1[Operations]
        - CALCULATE (
            VALUES ( Sheet1[Operations] ),
            FILTER (
                ALL ( Sheet1 ),
                Sheet1[Company] = EARLIER ( Sheet1[Company] )
                    && Sheet1[Country] = EARLIER ( Sheet1[Country] )
                    && Sheet1[Date] = FollowingDay
            )
        )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
SSS
Helper I
Helper I

PD.I would like to Point that there are many companies and countries (what I put was a smaller example in the post) and it is not feasable to put manually which company in which country must be searched by the function (that is why I used the "EARLIER" function)

Zubair_Muhammad
Community Champion
Community Champion

@SSS

 

Hi. Try this calculated column

 

=
VAR FollowingDay =
    NEXTDAY ( Sheet1[Date] )
RETURN
    Sheet1[Operations]
        - CALCULATE (
            VALUES ( Sheet1[Operations] ),
            FILTER (
                ALL ( Sheet1 ),
                Sheet1[Company] = EARLIER ( Sheet1[Company] )
                    && Sheet1[Country] = EARLIER ( Sheet1[Country] )
                    && Sheet1[Date] = FollowingDay
            )
        )

Regards
Zubair

Please try my custom visuals

View solution in original post

Great solution! Thanks Man.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors