cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## How to create a column that calculates the difference across two rows consecutively?

Hi,

Would like to seek some help here.

I have a series of data, say an electricity meter (KWH) reading that is being recorded everyday at a fixed time (12am).

I would like to set up a new column calculation in Power BI that calculates the difference between two consective rows; basically the next day's meter reading minus the current day's meter reading.

Can I get some help on how I can do this in Power BI?

My sample data looks like this:

 Meter (KWH) Daily Consumption (KWH) 1/8/2019 100000 100 2/8/2019 100100 150 3/8/2019 100250 200 4/8/2019 100450 150 5/8/2019 100600 250 6/8/2019 100850 100 7/8/2019 100950 200 8/8/2019 101150 250 9/8/2019 101400 200 10/8/2019 101600

- meter data is recorded everyday at 12am (start of day)

- the data recorded is in the 2nd column (Electricity Meter reading @ 12 AM (KWH))

- the column calculation I am trying to set up is the 3rd column (Daily Consumption (KWH))

- the consumption on 1/8/2019 is thus the meter reading (column 2) data on 2/8/2019 - 1/8/2019

- the last meter reading in this sample is on 10/8/2019 so the electricity consumption for this day is not calculated.

Thanks so much for the help!

Regards,

Aaron

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to create a column that calculates the difference across two rows consecutively?

Hi @ay80

Try this:

```NewCol =
VAR NextDay_ =
CALCULATE ( MIN ( Table1[Date] ), Table1[Date] > EARLIER ( Table1[Date] ), ALL(Table1) )
VAR NextDayVal_ =
CALCULATE ( DISTINCT ( Table1[Meter] ), Table1[Date] = NextDay_ , ALL(Table1))
VAR CurrentDayVal_ = Table1[Meter]
RETURN
IF ( NOT ISBLANK ( NaxtDay_ ), CurrentDayVal_ - NextDayVal_ )
```

3 REPLIES 3
Super User

## Re: How to create a column that calculates the difference across two rows consecutively?

Hi @ay80

Try this:

```NewCol =
VAR NextDay_ =
CALCULATE ( MIN ( Table1[Date] ), Table1[Date] > EARLIER ( Table1[Date] ), ALL(Table1) )
VAR NextDayVal_ =
CALCULATE ( DISTINCT ( Table1[Meter] ), Table1[Date] = NextDay_ , ALL(Table1))
VAR CurrentDayVal_ = Table1[Meter]
RETURN
IF ( NOT ISBLANK ( NaxtDay_ ), CurrentDayVal_ - NextDayVal_ )
```

Frequent Visitor

## Re: How to create a column that calculates the difference across two rows consecutively?

Thanks, @AlB , it worked!

Super User

## Re: How to create a column that calculates the difference across two rows consecutively?

Hi,

This calculated column formula works as well.

`=if(ISBLANK(CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))),BLANK(),LOOKUPVALUE(Data[Meter (KWH)],Data[Date],CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date]))))-Data[Meter (KWH)])`

Hope this helps.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 181 members 2,114 guests
Recent signins: