## Desktop

Frequent Visitor
Posts: 2
Registered: ‎04-28-2017

# 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.

 MTU Timestamp Read 41149978 03/12/2014 07:24:34 23618 41149978 03/12/2014 19:31:39 23777 41149978 03/13/2014 07:37:08 23925 41149978 03/13/2014 19:42:48 24103 41149978 03/14/2014 07:50:02 24256 41149978 03/14/2014 19:56:47 24397 41149978 03/15/2014 08:01:08 24516

Accepted Solutions
Super User
Posts: 1,468
Registered: ‎11-25-2016

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

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

Rename the new column "Consumpton"

Generated code:

```let
#"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:

Specializing in Power Query Formula Language (M)

All Replies
Super User
Posts: 1,468
Registered: ‎11-25-2016

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

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

Rename the new column "Consumpton"

Generated code:

```let
#"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:

Specializing in Power Query Formula Language (M)
Super User
Posts: 2,163
Registered: ‎07-22-2015

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

Output

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

## Re: Calculate monthly consumption from daily meter reads

[ Edited ]

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

Frequent Visitor
Posts: 2
Registered: ‎04-28-2017

## Re: Calculate monthly consumption from daily meter reads

Frequent Visitor
Posts: 2
Registered: ‎02-11-2019

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

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