Forum Discussion
Calculate difference from latest value
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"
Sorry, always forget to place a Buffer in order to prevent multiple calls.
Try to change your Source1-step to:
Source1 = Table.Buffer(MyTable),
Well, that does help! But unfortunately, it's still slow. The 'preview' has been running for a few minutes and nothing. I haven't been able to get a sense on how long it's actually going to take. PowerBI doesn't give you a whole lot of feedback on what it's doing...
Anyway, I'm thinking of creating an indexed view on SQL Server that does the calculations and then just read from the view on PowerBI. Does that sound reasonable? Would that query be ok to do in SQL?
Thanks :)
Sorry, I'm obviously a little bit slow on this one. There should be a substantial improvement in performance, if we "partition" the table before calling the function:
// Example data - replace with your table Source1 = MyTable, // Here the function is called #"Grouped Rows" = Table.Group(Source1, {"D", "E", "F", "G"}, {{"Data", each Table.AddColumn(_, "BValue", each SelectB([Selection], [Date])), type table}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"BValue", "Date", "true/false", "Type", "Value"}, {"BValue", "Date", "true/false", "Type", "Value"}), #"Expanded BValue" = Table.ExpandTableColumn(#"Expanded Data", "BValue", {"Value"}, {"ValueB"}), #"Added Custom1" = Table.AddColumn(#"Expanded BValue", "Difference", each if [Type]="A" then [Value]-[ValueB] else "") in #"Added Custom1"This is done by the Grouped Rows-step.
However, the view at the server might be the best idea.
Having a look at your filters in the function itself I was wondering about the true/false-filter, as it seems to contradict the logic you've described: As it actually reduced the returned rows to 1 date, there will be no dates before, hence no need to filter for the latest value and so on.
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 )
