Forum Discussion
Filtering last date for every year
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.
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
3 Replies
- amitchandakSuper User
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