topic Creating an energy accumulator from meter readings in Desktop
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1059670#M495119
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><TABLE><TBODY><TR><TD>DateTime</TD><TD>SocketID</TD><TD>SessionID</TD><TD>Energy</TD><TD> </TD><TD>Accuml Energy</TD></TR><TR><TD>01-Jan-20</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD> </TD><TD>1</TD></TR><TR><TD>01-Jan-20</TD><TD>2</TD><TD>1</TD><TD>10</TD><TD> </TD><TD>10</TD></TR><TR><TD>02-Jan-20</TD><TD>1</TD><TD>1</TD><TD>2</TD><TD> </TD><TD>2</TD></TR><TR><TD>02-Jan-20</TD><TD>2</TD><TD>1</TD><TD>11</TD><TD> </TD><TD>11</TD></TR><TR><TD>03-Jan-20</TD><TD>1</TD><TD>1</TD><TD>3</TD><TD> </TD><TD>3</TD></TR><TR><TD>03-Jan-20</TD><TD>2</TD><TD>1</TD><TD>12</TD><TD> </TD><TD>12</TD></TR><TR><TD>04-Jan-20</TD><TD>1</TD><TD>1</TD><TD>4</TD><TD> </TD><TD>4</TD></TR><TR><TD>04-Jan-20</TD><TD>2</TD><TD>1</TD><TD>13</TD><TD> </TD><TD>13</TD></TR><TR><TD>05-Jan-20</TD><TD>1</TD><TD>2</TD><TD>1</TD><TD> </TD><TD>5</TD></TR><TR><TD>05-Jan-20</TD><TD>2</TD><TD>2</TD><TD>1</TD><TD> </TD><TD>14</TD></TR><TR><TD>06-Jan-20</TD><TD>1</TD><TD>2</TD><TD>2</TD><TD> </TD><TD>6</TD></TR><TR><TD>06-Jan-20</TD><TD>2</TD><TD>2</TD><TD>2</TD><TD> </TD><TD>15</TD></TR><TR><TD>07-Jan-20</TD><TD>1</TD><TD>2</TD><TD>3</TD><TD> </TD><TD>7</TD></TR><TR><TD>07-Jan-20</TD><TD>2</TD><TD>2</TD><TD>3</TD><TD> </TD><TD>16</TD></TR><TR><TD>08-Jan-20</TD><TD>1</TD><TD>3</TD><TD>1</TD><TD> </TD><TD>8</TD></TR><TR><TD>08-Jan-20</TD><TD>2</TD><TD>3</TD><TD>1</TD><TD> </TD><TD>17</TD></TR><TR><TD>09-Jan-20</TD><TD>1</TD><TD>3</TD><TD>2</TD><TD> </TD><TD>9</TD></TR><TR><TD>09-Jan-20</TD><TD>2</TD><TD>3</TD><TD>2</TD><TD> </TD><TD>18</TD></TR><TR><TD>10-Jan-20</TD><TD>1</TD><TD>3</TD><TD>3</TD><TD> </TD><TD>10</TD></TR><TR><TD>10-Jan-20</TD><TD>2</TD><TD>3</TD><TD>3</TD><TD> </TD><TD>19</TD></TR></TBODY></TABLE><P> </P><P>In psuedo terms the algorithm is</P><P> </P><P>For Each SocketID</P><P><STRONG>if</STRONG> the SessionId has not changed since last readings <STRONG>then</STRONG> 'Accuml Energy' = (Energy-Energy of previous reading) + last 'Accuml Energy' value</P><P><STRONG>else</STRONG></P><P>Accuml Energy = Energy + last 'Accuml Energy' value</P><P> </P><P>There may be other algorithms that work...</P><P> </P><P>I think I may be able to use EARLIER somehow, but no yet worked it out </P><P> </P><P>Thanks for your help</P><P> </P>Thu, 30 Apr 2020 16:12:51 GMTwaynewoodhead2020-04-30T16:12:51ZCreating an energy accumulator from meter readings
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1059670#M495119
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><TABLE><TBODY><TR><TD>DateTime</TD><TD>SocketID</TD><TD>SessionID</TD><TD>Energy</TD><TD> </TD><TD>Accuml Energy</TD></TR><TR><TD>01-Jan-20</TD><TD>1</TD><TD>1</TD><TD>1</TD><TD> </TD><TD>1</TD></TR><TR><TD>01-Jan-20</TD><TD>2</TD><TD>1</TD><TD>10</TD><TD> </TD><TD>10</TD></TR><TR><TD>02-Jan-20</TD><TD>1</TD><TD>1</TD><TD>2</TD><TD> </TD><TD>2</TD></TR><TR><TD>02-Jan-20</TD><TD>2</TD><TD>1</TD><TD>11</TD><TD> </TD><TD>11</TD></TR><TR><TD>03-Jan-20</TD><TD>1</TD><TD>1</TD><TD>3</TD><TD> </TD><TD>3</TD></TR><TR><TD>03-Jan-20</TD><TD>2</TD><TD>1</TD><TD>12</TD><TD> </TD><TD>12</TD></TR><TR><TD>04-Jan-20</TD><TD>1</TD><TD>1</TD><TD>4</TD><TD> </TD><TD>4</TD></TR><TR><TD>04-Jan-20</TD><TD>2</TD><TD>1</TD><TD>13</TD><TD> </TD><TD>13</TD></TR><TR><TD>05-Jan-20</TD><TD>1</TD><TD>2</TD><TD>1</TD><TD> </TD><TD>5</TD></TR><TR><TD>05-Jan-20</TD><TD>2</TD><TD>2</TD><TD>1</TD><TD> </TD><TD>14</TD></TR><TR><TD>06-Jan-20</TD><TD>1</TD><TD>2</TD><TD>2</TD><TD> </TD><TD>6</TD></TR><TR><TD>06-Jan-20</TD><TD>2</TD><TD>2</TD><TD>2</TD><TD> </TD><TD>15</TD></TR><TR><TD>07-Jan-20</TD><TD>1</TD><TD>2</TD><TD>3</TD><TD> </TD><TD>7</TD></TR><TR><TD>07-Jan-20</TD><TD>2</TD><TD>2</TD><TD>3</TD><TD> </TD><TD>16</TD></TR><TR><TD>08-Jan-20</TD><TD>1</TD><TD>3</TD><TD>1</TD><TD> </TD><TD>8</TD></TR><TR><TD>08-Jan-20</TD><TD>2</TD><TD>3</TD><TD>1</TD><TD> </TD><TD>17</TD></TR><TR><TD>09-Jan-20</TD><TD>1</TD><TD>3</TD><TD>2</TD><TD> </TD><TD>9</TD></TR><TR><TD>09-Jan-20</TD><TD>2</TD><TD>3</TD><TD>2</TD><TD> </TD><TD>18</TD></TR><TR><TD>10-Jan-20</TD><TD>1</TD><TD>3</TD><TD>3</TD><TD> </TD><TD>10</TD></TR><TR><TD>10-Jan-20</TD><TD>2</TD><TD>3</TD><TD>3</TD><TD> </TD><TD>19</TD></TR></TBODY></TABLE><P> </P><P>In psuedo terms the algorithm is</P><P> </P><P>For Each SocketID</P><P><STRONG>if</STRONG> the SessionId has not changed since last readings <STRONG>then</STRONG> 'Accuml Energy' = (Energy-Energy of previous reading) + last 'Accuml Energy' value</P><P><STRONG>else</STRONG></P><P>Accuml Energy = Energy + last 'Accuml Energy' value</P><P> </P><P>There may be other algorithms that work...</P><P> </P><P>I think I may be able to use EARLIER somehow, but no yet worked it out </P><P> </P><P>Thanks for your help</P><P> </P>Thu, 30 Apr 2020 16:12:51 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1059670#M495119waynewoodhead2020-04-30T16:12:51ZRe: Creating an energy accumulator from meter readings
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1060809#M495637
<P>Hi <LI-USER uid="221559"></LI-USER> ,</P>
<P> </P>
<P>This involves loops, which I am researching. It will take much time. I will reply to you as soon as I research it.</P>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>Fri, 01 May 2020 09:57:04 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1060809#M495637Icey2020-05-01T09:57:04ZRe: Creating an energy accumulator from meter readings
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1061525#M495897
<P>Icey,</P><P> </P><P>Thanks. If ypu need any more info on the problem let me know</P><P>Wayne</P>Fri, 01 May 2020 19:20:22 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1061525#M495897waynewoodhead2020-05-01T19:20:22ZRe: Creating an energy accumulator from meter readings
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1065790#M496890
<P>Hi <LI-USER uid="221559"></LI-USER> ,</P>
<P> </P>
<P>Try this:</P>
<LI-CODE lang="markup">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 )
)
)
)</LI-CODE>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="acc.PNG" style="width: 574px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/265523i6F6EFC3538A3F033/image-size/large?v=1.0&px=999" title="acc.PNG" alt="acc.PNG" /></span></P>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>
<P> </P>
<P>If this post <STRONG>helps</STRONG>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Mon, 04 May 2020 08:31:25 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1065790#M496890Icey2020-05-04T08:31:25ZRe: Creating an energy accumulator from meter readings
https://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1066918#M497182
<P>Hi</P><P> </P><P>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.</P><P> </P><P>I am working on another route to correct on database entry, rather than reading, which should be quicker.</P><P> </P><P>Thanks for getting me this far!</P>Mon, 04 May 2020 14:04:44 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-energy-accumulator-from-meter-readings/m-p/1066918#M497182waynewoodhead2020-05-04T14:04:44Z