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:
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]&"")
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”
- To display as the “date” column, use FORMAT for instance:
IF('table'[argument]=MAX('table'[argument]),"value",FORMAT('table'[argument],"DD MMMM YYYY"))
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.
I will explain how to do with the year. The formula will be:
IF('table'[argument]=MAX('table'[argument]),"value",FORMAT('table'[argument],"YYYY"))
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"))
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...