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

Thanks for your help

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 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors