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 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!
Go to Solution.
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]
IF ( NOT ISBLANK ( NaxtDay_ ), CurrentDayVal_ - NextDayVal_ )
Thanks, @AlB , it worked!
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.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.