Calculate Monthly Usage using multiple filters in Power Query

[HopkiJ]

Hi Everyone,

I have built a system to manage my energy in Power Bi but I'm unable to figure out how to manage the manual meter readings within it. 

I receive monthly reads on different days across multiple sites and meters (but only one read each meter per month) I need to calculate the consumption per month per meter per site. An example is below. I've tried grouping and Max-Min, merging with the date table and filling down and non of these solutions is working. 

Any help would be greatly appreciated! 

Site Name	Meter Ref	Date	Reading
Site 1	Meter 11	25/05/2021	112
Site 2	Meter 2	27/05/2021	86504
Site 1	Meter 12	25/05/2021	889932
Site 3	Meter 3	22/05/2021	77846
Site 1	Meter 11	25/06/2021	1230
Site 2	Meter 2	27/06/2021	86558
Site 1	Meter 12	25/06/2021	889944
Site 3	Meter 3	22/06/2021	85846

[Anonymous · Accepted solution]

Hi @HopkiJ ,

Please follow the steps below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY4xCoAwEAS/IqkFk03ucnmElaVYprAV/49ENDlE0u3CMMy6mmU/8+DMaOZ85mNwZYImSxMsynEOZhsfEBUsC1FxwmRDI5USX6VISl5ZfWXLAhQaowT+tb6hXEPhba+UVSlJt5R1aQi90obSXbpd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Site Name" = _t, #"Meter Ref" = _t, Date = _t, Reading = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site Name", type text}, {"Meter Ref", type text}, {"Date", type text}, {"Reading", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "fr-DZ"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Site Name", Order.Ascending}, {"Meter Ref", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Site Name", "Meter Ref"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data",(x)=>Table.AddColumn(x,"Monthly Usage",each try (x[Reading]{[Index]}-x[Reading]{[Index]-1}) otherwise null )}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Date", "Monthly Usage"}, {"Date", "Monthly Usage"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous · Accepted solution]

Hi @HopkiJ ,

Please follow the steps below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY4xCoAwEAS/IqkFk03ucnmElaVYprAV/49ENDlE0u3CMMy6mmU/8+DMaOZ85mNwZYImSxMsynEOZhsfEBUsC1FxwmRDI5USX6VISl5ZfWXLAhQaowT+tb6hXEPhba+UVSlJt5R1aQi90obSXbpd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Site Name" = _t, #"Meter Ref" = _t, Date = _t, Reading = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site Name", type text}, {"Meter Ref", type text}, {"Date", type text}, {"Reading", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "fr-DZ"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Site Name", Order.Ascending}, {"Meter Ref", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Site Name", "Meter Ref"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data",(x)=>Table.AddColumn(x,"Monthly Usage",each try (x[Reading]{[Index]}-x[Reading]{[Index]-1}) otherwise null )}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Date", "Monthly Usage"}, {"Date", "Monthly Usage"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HopkiJ · Accepted solution]

Thank you Gao, the help is greatly appreciated.

I followed the above and it all looks correct except my "Monthly Usage" values have come out at null

Can you tell where I have gone wrong? 

= Table.TransformColumnTypes(#"Changed Type2", {{"Date", type date}}, "en-GB")
 

= Table.Sort(#"Changed Type with Locale",{{"Site Name", Order.Ascending}, {"Meter Ref", Order.Ascending}, {"Date", Order.Ascending}})

= Table.Group(#"Sorted Rows", {"Site Name", "Meter Ref"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1)}})
 

= Table.TransformColumns(#"Grouped Rows",{"Data",(x)=>Table.AddColumn(x,"Monthly Usage",each try (x[Reading]{[Index]}-x[Reading]{[Index]-1}) otherwise null )})
 

= Table.ExpandTableColumn(Custom1, "Data", {"Date", "Monthly Usage"}, {"Date", "Monthly Usage"})

[KT_Bsmart2gethe]

Hi @HopkiJ ,

This can be easily achieved through DAX.

Monthly Consumption =

VAR prevMth =

CALCULATE(

    SUM('Table'[Reading]),

    PARALLELPERIOD(

        'Table'[Date].[Date],

        -1,

        MONTH)

)

VAR currMth = SUM('Table'[Reading])

RETURN

currMth - prevMth

Regards

KT

[HopkiJ]

Regrettably this just aggregated the consumption but thank you!