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

[rcmv85]

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 KPI	Year	Company_Name	Value
KPI_A	2022	Company_A	158057000
KPI_A	2022	Company_B	null
KPI_A	2022	Company_C	null
KPI_A	2021	Company_A	136341000
KPI_A	2021	Company_B	46213000
KPI_A	2021	Company_C	250199000
KPI_A	2020	Company_A	127144000
KPI_A	2020	Company_B	43475000
KPI_A	2020	Company_C	222884000
KPI_A	2019	Company_A	155900000
KPI_A	2019	Company_B	55537000
KPI_A	2019	Company_C	252633000
KPI_B	2022	Company_A	-1981000
KPI_B	2022	Company_B	null
KPI_B	2022	Company_C	null
KPI_B	2021	Company_A	17937000
KPI_B	2021	Company_B	888000
KPI_B	2021	Company_C	15382000
KPI_B	2020	Company_A	-1279000
KPI_B	2020	Company_B	-8008000
KPI_B	2020	Company_C	8867000
KPI_B	2019	Company_A	47000
KPI_B	2019	Company_B	-141000
KPI_B	2019	Company_C	13886000

[FreemanZ · Accepted solution]

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]

)

[rcmv85]

Thank you @FreemanZ !

After some adjustments, it worked like a charm!