Forum Discussion
Solved
Matrix To Show Latest Order Date Per Line
Hello, I would like to have displayed the latest order date in a matrix I created without using the Subtotal/Grand Total options so that I will be able to use conditional formatting on the max da...
Hi eddya
You may get it in query editor.Then add an index column to sort the store column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZDLEsMgCEX/hXUcipqULNum73faneP//0ZIaMWZuziAVy+mBJvtbnDkRdAAIaFfUT+jx7AUrEUsrNhDbhLsD8eTa6NIJhGp2CPWWDj8LlX3+XK9LW+rm+1ULDGwtW5lvT+eL9exSCYS9Z8u1EiGrDhb3+Pn63wQ2cad4dqQ6y/JeQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductID = _t, Store1 = _t, Store2 = _t, Store3 = _t, Store4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"Store1", type date}, {"Store2", type date}, {"Store3", type date}, {"Store4", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max({[Store1],[Store2],[Store3],[Store4]})), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Last Sold Overall"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"ProductID"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Store"}, {"Value", "Date"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "Index", each if [Store] = "Store1" then 1 else if [Store] = "Store2" then 2 else if [Store] = "Store3" then 3 else if [Store] = "Store4" then 4 else 5) in #"Added Conditional Column"Regards,
eddya when you say latest order date, what does it mean? Latest order across all the orders doesn't matter which channel the order is place from?
Hi v-cherch-msft, let me know if this will suffice. Last sold overall is my expected resulted.
ProductID Store1 Store2 Store3 Store4 Last Sold Overall ABCD-12-12 1/1/2019 12/31/2018 12/4/2018 1/4/2019 1/4/2019 EFGH-54-54 4/11/2019 4/1/2019 4/11/2019 3/31/2019 4/11/2019 IJKL-12-14 4/8/2019 3/4/2019 1/5/2019 3/1/2019 4/8/2019 MNOP-68-68 2/3/2018 3/3/2018 3/1/2018 3/8/2018 3/8/2018 QRST-23-23 1/1/2016 1/1/2017 1/1/2018 1/1/2019 1/1/2019 Hi eddya
I would suggest you use 'Unpivot columns' in query editor.Then you may get the latest date in matrix.Attached sample file for your reference.
Regards,