cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mekkern Visitor
Visitor

Calculating consumption column using EARLIER function

I have power meeter readings from from 61 (M05-M65) sensors reporting every 10 min for a one week period, a total of 70875 records in my excel table. A meeter reading is reported for each sensor along with a time stamp.

 

Capture.PNG

 

I use the following formula to calculate a collumn with the difference between two consecutive meeter readings for a sensor to determine the power consumption for that sensor in that interval:

 

Increment_ = (IF(
ISBLANK(
calculate(
MAX(LCU_data[Power meter (Wm)]); ALL(LCU_data); LCU_data[Pole]=EARLIER(LCU_data[Pole]); LCU_data[RTC]<EARLIER(LCU_data[RTC])));0;
LCU_data[Power meter (Wm)]-calculate(MAX(LCU_data[Power meter (Wm)]); ALL(LCU_data); 
LCU_data[Pole]=EARLIER(LCU_data[Pole]); LCU_data[RTC]<EARLIER(LCU_data[RTC]))))

 

No suprise i run out of memory.

 

If i delete data from all but one sensor in excel and then import as a smaller table and alter the code accordingly i get what i want:

Increment_ = (IF(
ISBLANK(
calculate(
MAX(M05[Power meter (Wm)]); ALL(M05); M05[Pole]=EARLIER(M05[Pole]); M05[RTC]<EARLIER(M05[RTC])));0;
M05[Power meter (Wm)]-calculate(MAX(M05[Power meter (Wm)]); ALL(M05); 
M05[Pole]=EARLIER(M05[Pole]); M05[RTC]<EARLIER(M05[RTC]))))

 

Capture.PNG

I cant make individual tables for each sensor, but is there a way to do this calculation sequentially per sensor and not get into the issue with the EARLIER function running out of memory when performed on the whole table?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
DAX0110 Member
Member

Re: Calculating consumption column using EARLIER function

I would suggest doing this in the query editor with the following general steps :

 

1. start query1

2. sort by Pole and RTC columns (only if they aren't already sorted)

3. add an index column

4. add a custom column called "previndex" with the formula:  [index] - 1

5. duplicate the query to query2

6. merge query1 and query2 on query1[previndex] = query2[index] and query1[Pole] = query2[Pole]

7. calculate the difference between the two [Power meter (Wm)] columns

 

 

1 REPLY 1
Highlighted
DAX0110 Member
Member

Re: Calculating consumption column using EARLIER function

I would suggest doing this in the query editor with the following general steps :

 

1. start query1

2. sort by Pole and RTC columns (only if they aren't already sorted)

3. add an index column

4. add a custom column called "previndex" with the formula:  [index] - 1

5. duplicate the query to query2

6. merge query1 and query2 on query1[previndex] = query2[index] and query1[Pole] = query2[Pole]

7. calculate the difference between the two [Power meter (Wm)] columns