Filtering last date for every year

[elmer]

Hello!

I have a question and maybe the solution is simple, but I cannot find it.

I have different dataset that I import in Power Bi (they are essentially different excel files hosted in a sharepoint site).

Every excel has its own "creation date" that I use for different comparison in my Bi.

The problem is that for some graphs I need Power Bi to focus only on the last excel that was created for evey year.

Here's an example of my dataset:

Creation date	Various data
01/01/2019	A
31/12/2019	B
05/06/2020	C
30/11/2020	D
04/05/2021	E
09/09/2021	F

So in that case, the desired situation is a graph that represents only the "Various data" B, D and F (the last item for every year).

I tried to creat a custom colum in dax with IF and MAX:

LAST ITEM FILTER = IF(Dataset[Creation date].[Date]=MAX(Dataset[Creation date]),"Yes","No")

but I can only filter the last date of all the excel, but I cannot group it by year.

There's any solution?

Thanks in advance.

[amitchandak · Accepted solution]

@elmer , if need a new column

LAST ITEM FILTER =
var _year = Dataset[Creation date]
return
IF(Dataset[Creation date]=MAXX(filter(Dataset,year(Dataset[Creation date]) = _year)Dataset[Creation date] ),"Yes","No")

For new measure refer my video for two ways

closingbalanceyear , lastnonblankvalue

https://www.youtube.com/watch?v=yPQ9UV37LOU

[elmer]

It works! Thank you very much!

I just modify:

var _year = Dataset[Creation date].[Year]

and it works perfectly fine!

[elmer]

It works! Thank you very much!

I just modify:

var _year = Dataset[Creation date].[Year]

and it works perfectly fine!