How to subtract yesterday´s value from today´s and then multiplicate with a factor? (in M or DAX)

[IEPMost]

Hi,

I guess it´s simple but I´m not able to find a solution..

I have three columns [Date], [meter reader] and [Factor]; I need to subtract today´s value from yesterday´s for [meter reader] and then multiplicate the result with a factor. Here is my input:

Date	meter reader	Factor
04.09.2020	1200	0,5
05.09.2020	1400	0,6
06.09.2020	1600	0,3
07.09.2020	1650	0,5

My output should be like this:

Date	meter reader	Factor	Consumption
04.09.2020	1200	0,5	100
05.09.2020	1400	0,6	120
06.09.2020	1600	0,3	15
07.09.2020	1650	0,5

for the first row: (1400 - 1200) * 0,5 = 100

2nd row: (1600 - 1400) * 0,6 = 120

...

Hope you know what I mean! Thank you very much for any help! Cheers

[PhilipTreacy · Accepted solution]

Hi @IEPMost 

Copy/paste this into your Advanced Editor in Power Query.  Here's a sample PBIX file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRM7DUMzIwMlDSUTI0MgBRBnqmSrE6QDlTZDkTqJwZRM4MWc4MKmcMkTNHkTOFmxkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"meter reader" = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"meter reader", Int64.Type}, {"Factor", type number}}),
    Col = List.Combine({List.RemoveFirstN(Table.Column(#"Changed Type", "meter reader"),1),{0}}),
    Columns = List.Combine({Table.ToColumns(#"Changed Type"),{Col}}),
    #"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Changed Type"),{"Next"}})),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Consumption", each if [Next] <> 0 then ([Next]-[meter reader])*[Factor] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Next"})
in
    #"Removed Columns"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[IEPMost]

Hi @PhilipTreacy ,

almost.. I get an error in the last line (doesn´t recognize "meter reader" in the "added custom" line).
[Next] - [meter reader]

Why is that? Thank you a lot!!

[PhilipTreacy]

Hi @IEPMost 

That line is taking the name of the column meter reader as shown in your sample image.

If your real data has a different column name then you need to change the code to pick this up.  That is, if your column is actually called XYZ then change the code to [Next] - [XYZ].

NOTE: column names are case sensitive so meter reader is different to Meter Reader, so make sure the your column name and the code use the same case.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[IEPMost]

Hi @PhilipTreacy ,

thank you for your help! That wasn´t the problem; I changed the Code in my column names. Anyway, the column [next] was very helpful. I just subtracted my [meter reader] from [Next] and multiplied it by the factor. Thank you 🙂