cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Creating an energy accumulator from meter readings

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Creating an energy accumulator from meter readings

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.

4 REPLIES 4
Highlighted
Community Support

## Re: Creating an energy accumulator from meter readings

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

Highlighted
Helper I

## Re: Creating an energy accumulator from meter readings

Icey,

Thanks. If ypu need any more info on the problem let me know

Wayne

Highlighted
Community Support

## Re: Creating an energy accumulator from meter readings

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.

Highlighted
Helper I

## Re: Creating an energy accumulator from meter readings

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!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors