cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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:

image.png

 

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

image.png

 

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:

image.png

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @powerbi-learner,

 

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. Smiley Happy

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

m0.PNG

 

Regards

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Hi @powerbi-learner,

 

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. Smiley Happy

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

m0.PNG

 

Regards

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors