Need Help in Calculating the Read total by serial no

[Anonymous]

Hi All,

I need help in find the total read for an entire year of each installation number.

Here is a sample data and the output which i am looking for.

Number	Serial Number	Date	Reason	Read
9000002	1692360	6/13/2022 0:00	1	1932
9000002	1692360	5/12/2022 0:00	1	1872
9000002	1692360	5/9/2022 0:00	10	1863
9000002	1692360	4/11/2022 0:00	1	1671
9000002	1692360	3/11/2022 0:00	1	1230
9000002	1692360	2/10/2022 0:00	1	779
9000002	1692360	1/14/2022 0:00	1	499
9000002	1692360	12/13/2021 0:00	1	0
9000002	1692360	12/8/2021 0:00	21	0
9000002	1692424	12/7/2021 0:00	22	1086
9000002	1692424	11/12/2021 0:00	1	689
9000002	1692424	10/14/2021 0:00	1	350
9000002	1692424	9/14/2021 0:00	1	76
9000002	1692424	8/13/2021 0:00	1	11
9000002	1692424	8/12/2021 0:00	21	0
9000002	1573157	8/11/2021 0:00	22	14150
9000002	1573157	7/12/2021 0:00	1	14113
Output
9000002	1692360	6/13/2022 0:00	1	1932
9000002	1692360	8/13/2021 0:00	1	11
9000002	1692424	12/7/2021 0:00	22	1086
9000002	1692424	12/13/2021 0:00	1	0
9000002	1573157	8/11/2021 0:00	22	14150
9000002	1573157	7/12/2021 0:00	1	14113
Final Output
Number	total reads	Formula
9000002	3044	=(E88-E89)+(E90-E91)+(E92-E93)

[v-xiaotang]

Hi @Anonymous 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

So for each serial number you want to find the difference between the oldest and newest readings, and then you want to sum these differences?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLhCoUgDAXgV7n4O3BnM6f3VaL3f42sJK64xQ2MqPPhNtu2UOm8OCwBubJkak85QiIT84e+dL7Auapw2BdbrBE8i6Jvoo7gupUsrkgRmPfICleIKVjIFRxBk1CtLkBEmkCqL4D7cPEr/Ipavgxx9vKJ053XMX99p5J9gn56Q0m5WE10Qb3tQcjql1UtoH5NxZoSrKN+8vzHmFaVtu48jDElzD08Rq0xNYL2z+4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Serial Number" = _t, Date = _t, Reason = _t, Read = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Read", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Serial Number"}, {{"Rows", each _, type table [Number=nullable text, Serial Number=nullable text, Date=nullable datetime, Reason=nullable text, Read=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Delta", each List.First([Rows][Read])-List.Last([Rows][Read])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Delta", Int64.Type}})
in
    List.Sum(#"Changed Type1"[Delta])

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".