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

Calculate monthly consumption from daily meter reads

Hi,

 

Here is the data at hand. The data is essentially meter reads for a given meter (MTU is unique) over a period 3 years or so. I want the minimum meter read for a given month and the maximum meter read for a given month for a given MTU and then obtain the difference between the two. That will give me consumption for that month. So in the data below, min is 3/12 : 23618, max is 24,516 on 3/15. So consumption will be 24,516-23,618. Thanks in advance.

 

There are about 70 meters in total with similar data. There are two daily reads per meter. 

 

MTUTimestampRead
4114997803/12/2014 07:24:3423618
4114997803/12/2014 19:31:3923777
4114997803/13/2014 07:37:0823925
4114997803/13/2014 19:42:4824103
4114997803/14/2014 07:50:0224256
4114997803/14/2014 19:56:4724397
4114997803/15/2014 08:01:0824516

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Calculate monthly consumption from daily meter reads

In Power Query you can select column Timestamp and:

Add Column - Date - Year

Add Column - Date - Month

Then on the Transform tab - Select columns MTU, Year, Month and choose Group By

Add Operations Max and Min

On Add Column tab: select Max and Min columns and choose Standard - Subtract

Rename the new column "Consumpton"

 

Generated code:

 

let
    Source = #"Daily Meter Reads",
    #"Inserted Year" = Table.AddColumn(Source, "Year", each Date.Year([Timestamp]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Timestamp]), type number),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"MTU", "Year", "Month"}, {{"Max", each List.Max([Read]), type number}, {"Min", each List.Min([Read]), type number}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "Inserted Subtraction", each [Max] - [Min], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction",{{"Inserted Subtraction", "Consumption"}})
in
    #"Renamed Columns1"

 

Result:

Monthly consumption.png

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: Calculate monthly consumption from daily meter reads

In Power Query you can select column Timestamp and:

Add Column - Date - Year

Add Column - Date - Month

Then on the Transform tab - Select columns MTU, Year, Month and choose Group By

Add Operations Max and Min

On Add Column tab: select Max and Min columns and choose Standard - Subtract

Rename the new column "Consumpton"

 

Generated code:

 

let
    Source = #"Daily Meter Reads",
    #"Inserted Year" = Table.AddColumn(Source, "Year", each Date.Year([Timestamp]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Timestamp]), type number),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"MTU", "Year", "Month"}, {{"Max", each List.Max([Read]), type number}, {"Min", each List.Min([Read]), type number}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "Inserted Subtraction", each [Max] - [Min], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction",{{"Inserted Subtraction", "Consumption"}})
in
    #"Renamed Columns1"

 

Result:

Monthly consumption.png

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Calculate monthly consumption from daily meter reads

hi  there,

 

not sure you have a calendar table in your dataset or not, if not then you should add one. although i create  a table from your table and dropped it in a table visual to get the exepcted resulted, added on measure to get the difference between min and max.

 

Table

 

mtu1.PNG

 

Output

 

mtu2.PNG

 

DAX Calculation for Reading Measure

 

mu3.PNG

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Microsoft
Microsoft

Re: Calculate monthly consumption from daily meter reads

Hi @abhijeetbarve,

Have you resolved your problem? If you have, please mark the right replay as answer, so other people will benefit from it. If it hasn't, please feel free to ask any issue.

Best Regards,
Angelia

Highlighted
Regular Visitor

Re: Calculate monthly consumption from daily meter reads

Thanks for the reply.

Highlighted
Frequent Visitor

Re: Calculate monthly consumption from daily meter reads

I have a setup of data for which I need to calculate the following:

- Yearly Usage of a product 

- Average Monthly usage of a product

- Months of stock on hand

- Minimum Lead time

- Average Lead time

- Maximum Lead time

- Reorder Point

- Minimum on hand

- Safety Stock

 

I'm not too versed in DAX code and expressions. If anyone can suggest how I need to set up my data to calculate these and the code to which I need to enter to calculate these would be great. 

 

Thank you!

Highlighted
New Member

Re: Calculate monthly consumption from daily meter reads

Hello Guys,

The Energy Consumption Calculation is great by using Daily Substraction Max and Min from meter reading but I faced a problem which is the meter reading reached 999999 units will roll over or start over from 0 unit. Example in one day 0700pm the meter reading is 999838.58 units, 11pm the meter reading 551.86 units, therfore the Max and Min will calculate wrong Energy Consumption on that day. May I know how to handle this kind of data which the device will roll over? Sample Data Sheet as shown below

 
Thu, 10 May 2018 07:00:00 PM999838.58
Thu, 10 May 2018 07:30:00 PM999921.17
Thu, 10 May 2018 08:00:00 PM1.6
Thu, 10 May 2018 08:30:00 PM82.14
Thu, 10 May 2018 09:00:00 PM160.59
Thu, 10 May 2018 09:30:00 PM236.17
Thu, 10 May 2018 10:00:00 PM311.09
Thu, 10 May 2018 10:30:00 PM388.34
Thu, 10 May 2018 11:00:00 PM471.2
Thu, 10 May 2018 11:30:00 PM551.86

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors