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

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 322 members 3,278 guests