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,
I have a network connected power meter with 2 inputs. At regular intervals it uploads its readings to the database, which includes an "energy consumed since power on" (Energy in the table ) reading. There are 2 sockets read, so each day I get 2 readings uploaded. Problem is, if there is a power cut the meter starts from 0 again, but updates a "SessionId" to say it has had a reset.
What I want to do for each input is to accumulate energy over time, handling the power outages, and know how much energy is consumed per day. Note, in the table below there is one reading per day, in practice I get 1000's, 1 every few seconds.
DateTime | SocketID | SessionID | Energy | Accuml Energy | |
01-Jan-20 | 1 | 1 | 1 | 1 | |
01-Jan-20 | 2 | 1 | 10 | 10 | |
02-Jan-20 | 1 | 1 | 2 | 2 | |
02-Jan-20 | 2 | 1 | 11 | 11 | |
03-Jan-20 | 1 | 1 | 3 | 3 | |
03-Jan-20 | 2 | 1 | 12 | 12 | |
04-Jan-20 | 1 | 1 | 4 | 4 | |
04-Jan-20 | 2 | 1 | 13 | 13 | |
05-Jan-20 | 1 | 2 | 1 | 5 | |
05-Jan-20 | 2 | 2 | 1 | 14 | |
06-Jan-20 | 1 | 2 | 2 | 6 | |
06-Jan-20 | 2 | 2 | 2 | 15 | |
07-Jan-20 | 1 | 2 | 3 | 7 | |
07-Jan-20 | 2 | 2 | 3 | 16 | |
08-Jan-20 | 1 | 3 | 1 | 8 | |
08-Jan-20 | 2 | 3 | 1 | 17 | |
09-Jan-20 | 1 | 3 | 2 | 9 | |
09-Jan-20 | 2 | 3 | 2 | 18 | |
10-Jan-20 | 1 | 3 | 3 | 10 | |
10-Jan-20 | 2 | 3 | 3 | 19 |
In psuedo terms the algorithm is
For Each SocketID
if the SessionId has not changed since last readings then 'Accuml Energy' = (Energy-Energy of previous reading) + last 'Accuml Energy' value
else
Accuml Energy = Energy + last 'Accuml Energy' value
There may be other algorithms that work...
I think I may be able to use EARLIER somehow, but no yet worked it out
Thanks for your help
Solved! Go to Solution.
Hi @waynewoodhead ,
Try this:
Accuml Energy column =
VAR t =
FILTER (
'Table',
'Table'[SocketID] = EARLIER ( 'Table'[SocketID] )
&& 'Table'[DateTime] <= EARLIER ( 'Table'[DateTime] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Energy] ),
t
)
- CALCULATE (
SUM ( 'Table'[Energy] ),
FILTER (
t,
VAR nextdt =
CALCULATE (
Min ( 'Table'[DateTime] ),
FILTER ( t, 'Table'[DateTime] > EARLIER ( 'Table'[DateTime] ) )
)
RETURN
'Table'[SessionID]
= CALCULATE (
MAX ( 'Table'[SessionID] ),
FILTER ( t, 'Table'[DateTime] = nextdt )
)
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @waynewoodhead ,
Try this:
Accuml Energy column =
VAR t =
FILTER (
'Table',
'Table'[SocketID] = EARLIER ( 'Table'[SocketID] )
&& 'Table'[DateTime] <= EARLIER ( 'Table'[DateTime] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Energy] ),
t
)
- CALCULATE (
SUM ( 'Table'[Energy] ),
FILTER (
t,
VAR nextdt =
CALCULATE (
Min ( 'Table'[DateTime] ),
FILTER ( t, 'Table'[DateTime] > EARLIER ( 'Table'[DateTime] ) )
)
RETURN
'Table'[SessionID]
= CALCULATE (
MAX ( 'Table'[SessionID] ),
FILTER ( t, 'Table'[DateTime] = nextdt )
)
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Thanks for the help. The code seems to work, but as the data set gets bigger I quickly run out of memory - too many iterations to complete I think.
I am working on another route to correct on database entry, rather than reading, which should be quicker.
Thanks for getting me this far!
Hi @waynewoodhead ,
This involves loops, which I am researching. It will take much time. I will reply to you as soon as I research it.
Best Regards,
Icey
Icey,
Thanks. If ypu need any more info on the problem let me know
Wayne
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |