Creating an energy accumulator from meter readings

[waynewoodhead]

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

[Icey · Accepted 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.

[waynewoodhead]

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!

[Icey]

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

[waynewoodhead]

Icey,

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

Wayne