cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ay80 Frequent Visitor
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/2019100000100
2/8/2019100100150
3/8/2019100250200
4/8/2019100450150
5/8/2019100600250
6/8/2019100850100
7/8/2019100950200
8/8/2019101150250
9/8/2019101400200
10/8/2019101600 

 

- 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
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
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_ )

 

ay80 Frequent Visitor
Frequent Visitor

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

Thanks, @AlB , it worked!

Super User
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.

Untitled.png

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 181 members 2,114 guests
Please welcome our newest community members: