cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating difference between consecutive rows

Hi, I am new to Bi and having a hard time in calculating the difference between consecutive rows for a particular ID.

I want to caculate the difference between consecutive volume for a particular "Unique ID".

As the ID changes, the cumulative difference calculation should correspond to that particular "Unique ID"

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Calculating difference between consecutive rows

1. Add an index in the Query Editor.

2. Add a calculated column with the formula below.

```Column =
VAR currentIndex = [Index]
VAR currentVolume = [Volume.Volume]
VAR lastVolume =
CALCULATE (
MIN ( Table1[Volume.Volume] ),
FILTER (
ALLEXCEPT ( 'Table1', Table1[Unique ID] ),
Table1[Index]
= currentIndex - 1
)
)
RETURN
IF ( ISBLANK ( lastVolume ), 0, currentVolume - lastVolume )
```

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Highlighted
Super User I

## Re: Calculating difference between consecutive rows

@hmeegada Could you please post the sample data in copiable format and also provide the expected output which will be helpful.

Proud to be a Super User!

Highlighted
Microsoft

## Re: Calculating difference between consecutive rows

What's the expected result? Since the "Unique" is unique, you can just calculate the number of "Unique".

[Unique]    count('table'[Unique])

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Calculating difference between consecutive rows

Hi,  I have provided a sample data below for your reference and the last column is the expected result

The difference between consecutive rows should continue for a particular Unique ID and as soon as the Unique ID changes, the difference between consecutive rows should correspond to that particular Unique ID.

Thanks

Highlighted
Microsoft

## Re: Calculating difference between consecutive rows

1. Add an index in the Query Editor.

2. Add a calculated column with the formula below.

```Column =
VAR currentIndex = [Index]
VAR currentVolume = [Volume.Volume]
VAR lastVolume =
CALCULATE (
MIN ( Table1[Volume.Volume] ),
FILTER (
ALLEXCEPT ( 'Table1', Table1[Unique ID] ),
Table1[Index]
= currentIndex - 1
)
)
RETURN
IF ( ISBLANK ( lastVolume ), 0, currentVolume - lastVolume )
```

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft

## Re: Calculating difference between consecutive rows

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors