Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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_ )
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.
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_ )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |