Setting slicer to current Month & Year (September 2024)

[ssesham]

Hi All,

I am looking for a help to setup date slicer to current month and year like (September 2024) as shown below. Any advice please..!

[ssesham]

Any advice much appreciated please..!

[v-stephen-msft]

Hi @ssesham ,

I have below things need to confirm with you.

You mean you don't have September 2024 in your slicer, but you want to show it, right?

Maybe you filtered out the September 2024 dates because of the other filters, but you still want the slicer to show September 2024, right?

If this is the case above, then the workaround is to create a separate date table that is not related to other tables.

Then create measures as visual-level filters to filter the other tables for the new date slicer created from the new separate data table.

You can create this date table as follows.

date table = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),EOMONTH(TODAY(),0)),"MonthYear",FORMAT([Date],"mmmm yyyy"),"sort",FORMAT([Date],"yyyymm"))

There' a meaure as the visual-level filter in the table visual. The monthyear slicer is created from the date table. The relative date slicer is created from the main table.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ssesham]

Hi @v-stephen-msft  thanks for taking time to reply. 

It's not about showing the filter for "Sept 2024". I want date slicer to be defaulted always current month. 

[v-stephen-msft]

Hi @ssesham ,

If you want a default selection always displaying the current month in the slicer, you can do as follows.

Create a calcualted column in the date table.

Date 2 = 
VAR _currentmonthyear = FORMAT(TODAY(),"mmmm yyyy")
RETURN
IF (_currentmonthyear=[MonthYear]
,
"CurrentMonth",[MonthYear])

Use this calculated column to create a slicer. When you select the current month and the next time you open the report, the report defaults to the value for the current month.

Another workaround is to use the "Preselected Slicer" visual in Power BI, which allows you to determine which values users must select when opening a report based on another column or measure.

Here's the blog about the default value slicer, please refer to

How to dynamically select the default values we ne... - Microsoft Fabric Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ssesham]

Thank you so much, much appreciated your effort, my existing dashboard filter is not straight forward, I have custom date range filter which is already in place.  If i remove the measure col as shown in pic from the detail section, the filters will not work. I agreed that your solution that works well if there is no custom filter. By any chance can we make this default month to work in this situation?

[Idrissshatila]

Hello @ssesham ,

as @lbendlin  mentioned you need to create a column that will show you what's the current year and month and this would be current month else show the months.

ive done something like this for current date https://youtu.be/kVcStgZhnuw?si=VjFJIK_CSrvKaHHP

[lbendlin]

General pattern (adjust to your needs):
- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[ssesham]

Thanks @lbendlin , But the issue is i have customized filter whenever i am using new column that refers the customize filter then it filters the record. 

If i remove the column that has shown in below pic from the visual filters does not work, even though i have created relationship using (1:M)  or (M:M) the dates between two table (one from date filter  and other one from detail table  which has date).

So i am confused why it's not working. otherwise i can apply your solution for the default date.