How to display only latest month data when year is selected

[PBI_newuser]

Hi, I want to display only the latest month data when the year is selected. 

For example, when Year = 2021 is selected, just show Product C and when Year = 2022 is selected, just show Product D.

Product	Month
A	2021-10
B	2022-02
C	2021-11
D	2022-03

[Anonymous · Accepted solution]

Hi @PBI_newuser ,

Please refer to my  step to see if it helps you. 

Create a colun first.

year_ = YEAR('Table'[Month])

Then create a measure.

Measure = 
VAR answer =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year_] = SELECTEDVALUE ( 'Table'[year_] )
                && 'Table'[Month] >= SELECTEDVALUE ( 'Table'[Month] )
        )
    )
RETURN
    IF ( answer = MAX ( 'Table'[Product] ),1, 0 )

Then filter the data.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CMccown]

Hi Polly,

I am trying to do something similar but I only need to display the latest month value in a slicer or a card when the year is chosen and there is existing data. (The data itself shows up in matrix tables in a separate visual)

In other words:

if the year filter is 2022 then "Month" on the card would reflect the latest month that contained data. For what I'm doing as of today, we have data through July so the card or slicer would display "July".

For prior years (2021, 2020, etc.), the latest month would be December as there would be a full year's worth of data so the card or slicer would display "December".

Could you help?

Thank you!

[amitchandak]

@PBI_newuser , Assuming month year is in the given format only

Use a measure like this in visual or visual level filter

calculate(lastnonblankvalue(Table[Month], Countrows(Table)) )