Showing Latest Data

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])
)

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".