cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

DateTimeSocketIDSessionIDEnergy Accuml Energy
01-Jan-20111 1
01-Jan-202110 10
02-Jan-20112 2
02-Jan-202111 11
03-Jan-20113 3
03-Jan-202112 12
04-Jan-20114 4
04-Jan-202113 13
05-Jan-20121 5
05-Jan-20221 14
06-Jan-20122 6
06-Jan-20222 15
07-Jan-20123 7
07-Jan-20223 16
08-Jan-20131 8
08-Jan-20231 17
09-Jan-20132 9
09-Jan-20232 18
10-Jan-20133 10
10-Jan-20233 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
Community Support

Re: Creating an energy accumulator from meter readings

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 )
                    )
            )
        )

acc.PNG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Creating an energy accumulator from meter readings

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

Helper I
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
Community Support

Re: Creating an energy accumulator from meter readings

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 )
                    )
            )
        )

acc.PNG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
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!

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

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