I have recently had some discussions with regards on how to enable the default selection of a slicer which will always keep the slicer selection on the current month.

As is currently stands there is no out of the box way to change the slicer on a selection where it shows the actual date for each day. This is because currently you can only have the selection on a value that does not change at every data refresh.

My proposed solution below is to use a fixed value, which does not change each time the data is refreshed.

I always suggest that it is best practise to complete the fixed value in the Power Query Editor. The reason is twofold.

  • Firstly, it is a lot easier to use the Power Query M functions to get the output you require.
  • Second, it allows for better compression into the data model.

As always, I will use the working example below, where I want my slicer to always select the current month.

  • I already have got a date table created, which is another recommendation to have in every data model, and you can refer to my blog post Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query which explains how to create the date table.
  • Next, I create the column with Year-Month with the code below, and the reason for this is because there are 12 months that happen over every year, without having the Year and Month combination it will result in multiple “Current Months”
    Number.ToText( [Calendar Year]) & "-" & Number.ToText( [Calendar Month Number])

     

    • And this is what the column looked like.
  • My next column that I created was to get the current Year-Month combination, which I created with the following code below.
    • NOTE: The code below does appear to be quite long, but based on my blog post, I have parameters for the #”Time Offset in Hours”, this is so that I when the data is being refreshed in the Power BI Service, it will then update correctly and show the correct Year-Month combination.
      Number.ToText(Date.Year(DateTimeZone.FixedLocalNow()+ #duration(0,Number.From(#"Time Offset in Hours"),0,0))) & "-" & Number.ToText(Date.Month(Date.From(DateTimeZone.FixedLocalNow()+ #duration(0,Number.From(#"Time Offset in Hours"),0,0))))

       

    • And this is what the column looked like.
  • The final step in my process is to then create a conditional column to compare the two columns above and where they match it will then be the current month, and when it is not, it is another month.
    • Which results in a column which will then give me a fixed value which says “Current Month”
    • And this then allows me to use this in a slicer, so that every time the data is refreshed the fixed value of “Current Month” does not change, but it will move as time moves.
    • As an option I then removed the previous 2 columns that I used because they are no longer needed.

As always, I am sure that there is more than one way to do that, but for me completing it in each step allows me to view and see that it is working as expected. And then to ensure that I am getting the results I expect.

Comments or suggestions are welcome in the section below.