Default Your Slicer to the Current Month in Power BI

First Published November 2022 on our YouTube Channel

Join 65,000+ subscribers to stay up to date with new videos release every week.

Is it possible to default your slicer to the current month? No.

But wait..you can..and here's a breakdown on how to do just that.

Watch the video or read on below:

The End Result

Let's start by looking at the end result. We have a month slicer that displays an option for the current month. This feature can be especially handy when you want to keep your data up to date, whether you're manually refreshing it or have it set to auto-refresh.?

Once the data is refreshed, the slicer will automatically update to show the new current month, ensuring that you're always viewing the most recent data.

Adjusting Your Power Query Calendar

The magic behind this is a special calendar table built in Power Query, equipped with handy columns such as 'Months since today', 'Weeks since current month', and a few others.

You can set up this calendar so that whenever the 'Months since today' column hits zero (which signifies the current month), it outputs the word 'Current'. This is done using a simple conditional formula.?

You can download the file and take a look at the code here

The table also has a 'Sort by' column, which helps to ensure that 'Current' is always the first option in your slicer.

Understanding "Current"

Before you proceed, it's important to clarify what you mean by 'current month'.?

Are you referring to 'today' as the current date every time you refresh your data, or do you mean the latest transaction date as 'current'? Depending on your definition, you might need to adjust your calendar table slightly.

For instance, if you're going by the actual current date, you can set up your calendar table to reflect this using Date.From(DateTime.LocalNow() ).

Alternatively, if you're referring to the latest transaction date, you can use the 'List.Max' function to retrieve the maximum date from your Fact table.

Sort by Column

Remember that for this slicer to work as intended, you need to make sure the 'Sort by' column is properly set up in your slicer. This is the column that determines the order of the options in your slicer and makes sure 'Current' shows up first.

Things to Keep in Mind

While this is a handy feature, there are a couple of things to keep in mind. Firstly, the 'current' month will actually be missing from your slicer, since it's being replaced by the 'Current' option. Secondly, if you want to switch years, you'll need to click off 'Current' before you can do so.

Conclusion

This is a feature I've been experimenting with and so far, it's proving to be quite useful. Let me know what you think in the comments! See you in the next video!

You can download the file and take a look at the code here

SideNote:

The crazy thing about this article is that I didn't write it. I copied the transcript from my YouTube vidoe and pasted into ChatGPT and used a prompt to write a blog post from a YouTube transcript. It then generated the content which I modified slightly and uploaded screen shots. It's pretty amazing. For more GPT4 content I have a YouTube playlist here:

Wyn

wyn.bio.link