How to show just latest year of data on chart (source table has 5 years of data)

[Anonymous]

Hello BI experts 🙂

I have a bar and column charts with values in them.

The values are drawn from 5 years of data collection, 2012 to 2016.

How do I make the charts automatically show just the latest year of data, i.e. just 2016?

I know I can go to Visual Level filters and click the latest year manually, but I need Power BI to select the latest year automatically.

I don't want the chart to sum all the data from 5 years.

Please explain like I'm 5 years old 🙂 Thanks.

Here is a picture of my report:

And here is the table of source data in Query Editor:

I wish there was a filter in Power BI visuals like there is in the Query Editor i.e. one that shows just the latest year, as shown here:

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

If I understand you correctly, you should be able to use the formulas below to create two measures to calculate total Enrolled, and total Base Population for the latest year, and show the measures as Values on the chart to get your expected result. 

Total Enrolled =
VAR latestYear =
    MAX ( 'COHs data 2012-2016'[Year] )
RETURN
    CALCULATE (
        SUM ( 'COHs data 2012-2016'[Enrolled] ),
        FILTER ( 'COHs data 2012-2016', 'COHs data 2012-2016'[Year] = latestYear )
    )

Total Base Population =
VAR latestYear =
    MAX ( 'COHs data 2012-2016'[Year] )
RETURN
    CALCULATE (
        SUM ( 'COHs data 2012-2016'[Base Population] ),
        FILTER ( 'COHs data 2012-2016', 'COHs data 2012-2016'[Year] = latestYear )
    )

Regards