cancel
Showing results 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

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 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
1 ACCEPTED SOLUTION
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]
)
2 REPLIES 2
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]
)
Helper I

Thank you @FreemanZ !

After some adjustments, it worked like a charm!