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

[Anonymous]

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

[Ashish_Mathur]

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.

[AlB · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

Thanks, @AlB , it worked!