Filter latest data as per slicer slicer selection

[Anonymous]

I have KPI data for stores for different month and I have a month Slicer.

My data looks something like this.

Store_Number  Month         Score

S1                      July-2019     1

S1                      Aug-2019     0

If I select July, then I should get score as 1 and if I select July and Aug in the slicer, I should see just 0- since that is the score based on the last visit to the store.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use the maximum of date to find the correct score. Here is a measure for your reference.

Measure =
LOOKUPVALUE (
    'Table'[Score],
    'Table'[Month], CALCULATE ( MAX ( 'Table'[Month] ), ALLSELECTED ( 'Table'[Month] ) )
)

[Anonymous]

LookupValue function doesn't work for a measure. Score here is a measure. Can you please suggest what can be used for a measure?

[mussaenda]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lHyKs2p1DUyMLQEsg2VYnWgwo6l6TBRA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store_Number = _t, Month = _t, Score = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Month", "Month - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Month", type date}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type", "Month", "Month - Copy - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column2",{{"Month - Copy - Copy", Date.Month, Int64.Type}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "Month", "Month - Copy.1"),
    #"Extracted Year" = Table.TransformColumns(#"Duplicated Column1",{{"Month - Copy.1", Date.Year, Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Year", {{"Month - Copy.1", type text}, {"Month - Copy - Copy", type text}}, "en-GB"),{"Month - Copy.1", "Month - Copy - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Based on the data you provided, you can try the code above:

[Anonymous]

Hi @Anonymous Please use below measure 

MaxOfSelectedMonth = CALCULATE(MAX(Data[Score]),ALLSELECTED(Data[Month]))