Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Month Filter-Preselected Current month

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 wou...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

     

    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's avatar
Joe_Barry
Solution Sage
1 year ago

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