Forum Discussion

elmer's avatar
elmer
Helper I
4 years ago
Solved

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 dateVarious data
01/01/2019A
31/12/2019B
05/06/2020C
30/11/2020D
04/05/2021E
09/09/2021F

 

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

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

3 Replies

  • 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's avatar
      elmer
      Helper I

      It works! Thank you very much!

       

      I just modify:

       

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

      and it works perfectly fine!

    • elmer's avatar
      elmer
      Helper I

      It works! Thank you very much!

       

      I just modify:

       

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

      and it works perfectly fine!