Month Filter-Preselected Current month

[LiebmannA]

Hi All, 

I have a month filter in a dataset under 'dimUmsatzDatum[Month Name]'
This only shows the month name and as a normal slicer people can select it according to their need. 

It would be amazing if the slicer when the user opens up the report would pre-select the current month automatically. 
The users can change it when needed, but as a preset the current month should be there when entering the report.

Thank you for your insights in advance, 
Attila L.

[danextian]

Hi @LiebmannA 

Several ways to do this.

Most of what everone would  suggest is to create a calculated column that checks whether a date is in the current month and return say "current month" otherwise the month name. That, thought, doesn't dynamically select the current month name but of course the word current month.

Another method leverages the Group by Columns property to "store a filter by using an alternate value, which represents the key of the entity" using Tabular editor. Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically. For instance, if May-24 is selected in the visual and currently has a key of 0, when June arrives, May will shift to a key of 1, and June will take the key of 0. Consequently, the slicer selection will automatically change to June.  Please refer to this YouTube video - https://www.youtube.com/watch?v=MrEAZREQuXM 

Please be aware that regardless of the method you use, if persistent filter is enabled, Power BI will load the previous slicer selection of the user the next time they open the report in the service.

[Joe_Barry]

Thanks for this! Much cleaner solution

[DataNinja777]

Hi @LiebmannA ,

To pre-select the current month in your dimUmsatzDatum[Month Name] slicer when the report loads, you need a calculated column in dimUmsatzDatum that determines whether a row belongs to the current month. Since dimUmsatzDatum[Month Name] contains only the month name, you should ensure that both the month and year are checked to avoid ambiguity.

Create the following calculated column in dimUmsatzDatum:

IsCurrentMonth = 
    IF(
        FORMAT(TODAY(), "MMMM") = 'dimUmsatzDatum'[Month Name] &&
        YEAR(TODAY()) = YEAR('dimUmsatzDatum'[Date]),
        1,
        0
    )

After creating this column, add dimUmsatzDatum[Month Name] to the slicer. In the Filters on this Visual pane, apply a filter where IsCurrentMonth = 1. This ensures that the slicer defaults to the current month when the report is opened, while still allowing users to change the selection if needed.

Alternatively, if your dataset contains a full date column, you can achieve the same effect using relative date filtering. Instead of Month Name, use dimUmsatzDatum[Date] in the slicer and apply a relative date filter set to "This Month." This approach ensures the report opens with the current month selected while maintaining user flexibility.

This method provides an automatic pre-selection of the current month without requiring bookmarks or manual adjustments.

Best regards,

[Joe_Barry]

Hi Attila

Do you also have a date in your table in this format?  "03/02/2024"? In Power Query create a custom column with this code

if [Date] >= Date.From(Date.StartOfMonth(
                   DateTime.LocalNow( ) 
                  ) )
                  
                  and [Date] <= Date.From(Date.EndOfMonth(  
                   DateTime.LocalNow( ) )
                  )
     then "Current Month"
     else [Month Name]

If you don't have a date, create one in Power Query as custom column, but you will need to create one for month number. convert to text

= if [MonthName] = "January" then 1
  else if [MonthName] = "February" then 2
  else if [MonthName] = "March" then 3
  else if [MonthName] = "April" then 4
  else if [MonthName] = "May" then 5
  else if [MonthName] = "June" then 6
  else if [MonthName] = "July" then 7
  else if [MonthName] = "August" then 8
  else if [MonthName] = "September" then 9
  else if [MonthName] = "October" then 10
  else if [MonthName] = "November" then 11
  else if [MonthName] = "December" then 12
  else null

then add another

"01/" & MonthNumber & Text.From([Year])

Then use this column for the date part in the first code. After loading the data, sort the new column by month Month Number

Thanks

Joe