Value of latest update for each measure when last update was in a different month

[LuizUK]

Hello Community,

What DAX function would you recommend to get the value corresponding to latest update of a list of KPIs where some of these KPIs are measured on monthly? Some are monthly, others quarterly and so on.

Note that the latest date in Last Update column is different for each metric.

I can't use a formula where the Metric is defined as a string (i.e. "A") because the metrics have long descriptions (some with more than 20 words) and my dataset has hundreds of different metrics.

The only solution I found so far is "slice" the data set in multiple smaller tables and it is getting impossible manage everything.

I appreciate any suggestion or ideas.

KR

[LuizUK]

Hey @tamerj1  thank you so much for your response. It worked brilliantly

Any guesses on how I can use the results of this table in a card? Naturally, after I filter to have just one metric?

[tamerj1]

@LuizUK 
Not sure what are you trying to display in that card but you can place a measure like

Sum of MAX Values =
SUMX (
    FILTER (
        'Table',
        'Table'[Last Update]
            = CALCULATE (
                MAX ( 'Table'[Last Update] ),
                ALLEXCEPT ( 'Table', 'Table'[Metric] )
            )
    ),
    'Table'[Value]
)

Actually in this case you don't need to have the filter measure, just use this measure to filter the table visual and it should work.

[tamerj1 · Accepted solution]

Hi @LuizUK 

Create the following filter measure, place it in the filter pane of the table visual and select 'is not blank' then apply the filter 

FilterMeasure =
COUNTROWS (

FILTER (

'Table',

'Table'[Last Update] = CALCULATE ( MAX ( 'Table'[Last Update] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) )

)

)