cancel
Showing results for
Did you mean:
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             Apple                 250
01/01/2017    Europe              France           Microsoft            400
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             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
Super User

@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

3 REPLIES 3
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)

Super User

@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

Helper I

Great solution! Thanks Man.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors