Power BI: default last year date selection on filter

This article explains how to set at default the current or last day or year of a slicer, no matter if new data will be added, the slicer will always select the last/current date including if I reset my report. I have this simple date table:

power bi power bi

When I create a filter with a single selection, by default, Power BI selects the older date and not the last or current one (01/01/2023). Of course, if I select the last one and save my report, 01/01/2023 will be selected as default but what happens if a new date will be added (01/01/2024) ? Well it will not be the default one except if I do it manually but the goal is to do it automatically. To do that, I will add a new column with this formula:

IF('table'[argument]=MAX('table'[argument]),"value",'table'[argument]&"")

power bi

NOTE:

  • Replace table, argument and value by yours. In my picture, I don’t specify the table because the argument is at the same table
  • If the argument column is “text”, you will have to change it into “date” or “number”
    power bi
  • To display as the “date” column, use FORMAT for instance:
    IF('table'[argument]=MAX('table'[argument]),"value",FORMAT('table'[argument],"DD MMMM YYYY"))
power bi power bi

Now if I create a new filter using this new column and select “current date” as default, no matter if new data will be added, the last date will be always selected by default.

power bi

I will explain how to do with the year. The formula will be:

IF('table'[argument]=MAX('table'[argument]),"value",FORMAT('table'[argument],"YYYY"))

power bi

As you can see, it is the same formula but if I want the previous year as default, I will use this formula:

IF('table'[argument].[Year]=MAX('table'[argument].[Year])-1,"previous year",FORMAT('table'[argument],"YYYY"))

power bi

Interesting Topics