Matrix To Show Latest Order Date Per Line

[eddya]

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 date as well. Here is a screenshot of my expected results:

thank you!

[parry2k]

@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?

[eddya]

@parry2k Yes, that's correct

[v-cherch-msft]

Hi @eddya 

Could you paste some sample data which could reproduce your scenario?

Regards

[eddya]

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

[v-cherch-msft]

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,

[eddya]

Is there any way to accomplish this without using the total option? I would like to be able to rename the field as well as using conditional formatting, which is not possible in Total fields.

[v-cherch-msft · Accepted solution]

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,