Forum Discussion
Calculate difference from latest value
- 10 years ago
This is one possibility:
let // This function selects the corresponding B-Row for every A value SelectB = (selection, date) => let // several filters following
// omitt this first filter if you don't have the true/false-field #"Filtered Rows3" = Table.SelectRows(ChgType, each ([#"true/false"] = "true")), // without the true/false-field, the next filter will look like this:
// #"Filtered Rows" = Table.SelectRows(ChgType, each ([Selection] = selection)),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows3", each ([Selection] = selection)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] <= date), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Type] = "B")), // Just keep the latest row from this selection
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Filtered Rows2",{{"Date", Order.Descending}})), #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1) in #"Kept First Rows", // Example data - replace with your table Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDTMzIwNFPSUVJwBBImIIZCBJAsKSpNVYrVgSgyRVFkhF2RCVyRE5Awxq7IGMUkU7CiSNyKnODWgRSlJeYUI1QZEWOfIYp9xtjtgysCGWSIpiYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Value = _t, Selection = _t, #"true/false" = _t]), ChgType = Table.Buffer(Table.TransformColumnTypes(Source1,{{"Date", type date}, {"Type", type text}, {"Value", Int64.Type}, {"Selection", type text}})), // Here the function is called #"Added Custom" = Table.AddColumn(ChgType, "BValue", each SelectB([Selection], [Date])), #"Expanded BValue" = Table.ExpandTableColumn(#"Added Custom", "BValue", {"Value"}, {"ValueB"}), #"Added Custom1" = Table.AddColumn(#"Expanded BValue", "Difference", each if [Type]="A" then [Value]-[ValueB] else "") in #"Added Custom1"
ImkeF
Nice catch on that date part, that's obviously wrong. There's a timestamp field and a field with the latest timestamp for that specific category - that's because some records are updated with different values, but I want to keep the historical data, so they are added and can be told apart with the timestamp. Now, I only want to use the records with the latest timestamp amongst their category (category/selection is a set of columns). The way I handled that was to write the max timestamp for its category (using group by) in each record as an extra column. If timestamp = latest timestamp (this is the true/false logic), this is the latest record for that category, thus the one we want to use.
To sum all of this up, the true/false line doesn't use Date but Timestamp.
I'm going to try your modifications in a few minutes, before trying the view alternative.
What's your datasource? There might be a smart native query you can write to make it faster. (Like using LAG function for SQL Server https://msdn.microsoft.com/en-us/library/hh231256.aspx )
- gdssiqueira10 years agoHelper I
- arify10 years agoMicrosoft Employee
Sorry, I didn't fully read your question. LAG wouldn't help, this looks pretty complicated.
- gdssiqueira10 years agoHelper I
- arify10 years agoMicrosoft Employee
Glad I could help somehow :)