Showing Latest Data

[Love2phish]

Hello,

We have 5 different status we are tracking across our locations. We check one or 2 of those per month. Example shown here:

Location	A Status	B Status	C Status	D Status	E Status	Contact Date
Location 1	1			2		3/12/2024
Location 2	5	5	5		3	3/15/2024
Location 2			3			4/1/2024
Location 3		5				4/6/2024
Location 1	4	2		3	2	4/10/2024

I would like to be able to show a table with a combined latest status for each location. For example based on the table above I would like to see the following output.

Location	A Status	B Status	C Status	D Status	E Status	Contact Date
Location 1	4	2		2		4/10/2024
Location 2	5	5	3		2	4/1/2024
Location 3		5				4/6/2024

[Sahir_Maharaj · Accepted solution]

Hello @Love2phish,

Can you please try this:

1. Find the most recent 'Contact Date' for each location

Latest Contact Date = 
CALCULATE(
    MAX('Table'[Contact Date]), 
    ALLEXCEPT('Table', 'Table'[Location])
)

2. For each status (A, B, C, D, E), create a measure to retrieve the latest non-blank status based on the latest contact date.

Latest A Status = 
CALCULATE(
    LASTNONBLANK('Table'[A Status], 'Table'[A Status]),
    'Table'[Contact Date] = [Latest Contact Date],
    ALLEXCEPT('Table', 'Table'[Location])
)

[lbendlin · Accepted solution]

You may want to consider unpivoting your data. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9TMFTSUQJhBSg2gtLG+oZG+kYGRiZKsTpIykHSpkgYrBSi3BSHcgUkZTC2ib4hFtUwFaZIKiGqzbCoBrnaBNnFUDbQbAOo8lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location " = _t, #"A Status" = _t, #"B Status" = _t, #"C Status" = _t, #"D Status" = _t, #"E Status" = _t, #"Contact Date" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location ", "Contact Date"}, "Status ID", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Contact Date", type date}})
in
    #"Changed Type"

Then you can create a cross join between locations and status IDs

Table 2 = CROSSJOIN(values('Table'[Location ]),values('Table'[Status ID]))

Then you can calculate the latest status for each combination

Note that I (ab)used the column total for the "Last contact date".

[Sahir_Maharaj · Accepted solution]

Hello @Love2phish,

Can you please try this:

1. Find the most recent 'Contact Date' for each location

Latest Contact Date = 
CALCULATE(
    MAX('Table'[Contact Date]), 
    ALLEXCEPT('Table', 'Table'[Location])
)

2. For each status (A, B, C, D, E), create a measure to retrieve the latest non-blank status based on the latest contact date.

Latest A Status = 
CALCULATE(
    LASTNONBLANK('Table'[A Status], 'Table'[A Status]),
    'Table'[Contact Date] = [Latest Contact Date],
    ALLEXCEPT('Table', 'Table'[Location])
)

[Love2phish]

Thank you this solved it for me.