Forum Discussion
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
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_ )
3 Replies
- AlB
Community Champion
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_ )Thanks, AlB , it worked!
- Ashish_Mathur
Super User
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.
