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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rcmv85
Helper I
Helper I

How to get the most recent year of data available for each column?

Hi!

I have a table that looks something like the one below after some transformations on power query.

Now, on a visualization, I need to get for each different company the most recent year with available data. For example, for Company_A it would be 2022 but for Company_B it would be 2021. How can I do this?

 

 

Financial KPIYearCompany_NameValue
KPI_A2022Company_A158057000
KPI_A2022Company_Bnull
KPI_A2022Company_Cnull
KPI_A2021Company_A136341000
KPI_A2021Company_B46213000
KPI_A2021Company_C250199000
KPI_A2020Company_A127144000
KPI_A2020Company_B43475000
KPI_A2020Company_C222884000
KPI_A2019Company_A155900000
KPI_A2019Company_B55537000
KPI_A2019Company_C252633000
KPI_B2022Company_A-1981000
KPI_B2022Company_Bnull
KPI_B2022Company_Cnull
KPI_B2021Company_A17937000
KPI_B2021Company_B888000
KPI_B2021Company_C15382000
KPI_B2020Company_A-1279000
KPI_B2020Company_B-8008000
KPI_B2020Company_C8867000
KPI_B2019Company_A47000
KPI_B2019Company_B-141000
KPI_B2019Company_C13886000
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @rcmv85 

try to create a table visual with Company_Name column and a measure like:

MostRecentYear =
SUMX(
   FILTER(
        TableName,
        TableName[Value] <> BLANK()
   ),
   TableName[Year]
)

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @rcmv85 

try to create a table visual with Company_Name column and a measure like:

MostRecentYear =
SUMX(
   FILTER(
        TableName,
        TableName[Value] <> BLANK()
   ),
   TableName[Year]
)

Thank you @FreemanZ !

After some adjustments, it worked like a charm!

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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