Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lEy1DfUNzIwMgAxDZRidaKVfDMTczPBMsZwGVOwjI8jWNgEJmyErsEULmOMrMEcbo4hWBjFeguYpJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type date}, {"Sales", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each let thisdate = [Date], thiscity = [City] in Table.FirstN(Table.SelectRows(#"Changed Type", each [Date] < thisdate and [City] = thiscity),1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Sales"}, {"Date.1", "Sales.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Date.1", "Prev Date"}, {"Sales.1", "Prev Sales"}})
in
#"Renamed Columns"
Regards,
Pat
Did I answer your question? Mark my post as a solution! Kudos are also appreciated! To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
