Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-ljerr-msft
Employee
Employee

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
v-ljerr-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.