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