Month + Year slicer in Power BI (or restrict selection to first day of month)

[arr2025]

Hi everyone,

I’m trying to create a Month + Year slicer in Power BI (or Microsoft Fabric) that’s easy for users to interact with.

Here’s what I need:

It should show only months (e.g., Jan 2024, Feb 2024…or 2024/01, 2024/02)

Users should be able to drag or select a range easily

I don’t want multiple clicks like the standard date hierarchy slicer

Minimal setup for end-users

I’ve tried a few things so far:

Using a MonthStartDate column → still shows daily values in the slider

Using a numeric YYYYMM column → the slider fills in numbers I don’t want

Using text MMM YYYY → no slider option

Custom DAX columns → still behaves like a continuous scale

To simply put, I don’t want something like the standard date hierarchy slicer or like below screenshot. If users have to interact at the date level, they should be restricted so they can only select the first day of the month.

I’ve seen some custom visuals like the Advanced Time Slicer, but I’m curious if anyone has a built-in solution or a simple trick/workaround that works well for this kind of monthly range selection.

If you’ve done something similar or know a clean way to make this work, I’d really appreciate any guidance or tips!

Thanks so much!

[v-prasare]

Hi @arr2025,

we are following up regarding your question. Could you please confirm if the responses provided by the community members helped resolve your query? If not, please let us know we are happy to assist further.

Thanks,

Prashanth Are

MS fabric community support

[v-prasare]

Hi @arr2025,

we are following up regarding your question. Could you please confirm if the responses provided by the community members helped resolve your query? If not, please let us know we are happy to assist further.

@danextian, @PhilipTreacy & @Ahmed-Elfeel, Thanks for your prompt response

Thanks,

Prashanth Are

MS fabric community support

[PhilipTreacy]

Hi @arr2025 

Download example PBIX file with the data and examples shown below.

So you want to show a list of all Month-Years?  Like so:

Not sure how many years you have but that list could get very long.

You could try using 2 tables with Year in one and Month in the other

With my sample data

You can use these 2 tables as slicers to show for example May 2024 - hold CTRL when selecting the month then the year

Or a range of months, say Jan through to Apr 2025, again use CTRL to multi-select whilst clicking

Regards

Phil

[arr2025]

Thanks for the response.

However, whenever I use the Between slicer, it becomes a continuous date slider (as shown in the screenshot). My data is at the month level, so I want users to be able to select by Month and Year, not at the day level.

I'd okay if user can select 8/1/2016 to 11/1/2023 instead.

I am aware that I can use two separate slicers (one for Year and one for Month), but this approach is not ideal for my requirements.

I also tried using a list slicer, but if a user wants data from 2010 to 2025, they would have to select many individual years, which is not ideal either.

Is there any other way to achieve a simple Month + Year range selection without all the extra clicks?

Thanks in advance for any guidance.

[danextian]

Hi @arr2025 

There’s currently no option to make the slider slicer show only the exact dates that appear in your column. It uses a continuous range, so it automatically fills in every date between the minimum and maximum values it finds. That’s why it behaves this way. I would just apply conditional formatting to the slicer’s header text to make this clearer to users and reduce confusion.

Periods Selected = 
VAR _min =
    MINX ( ALLSELECTED ( Dates[Start of Month] ), [Start of Month] )
VAR _max =
    MAXX ( ALLSELECTED ( Dates[Start of Month] ), [Start of Month] )
VAR _minPeriod =
    FORMAT ( _min, "mmm yyyy" )
VAR _maxPeriod =
    FORMAT ( _max, "mmm yyyy" )
RETURN
    "Periods selected: "
        & IF ( _minPeriod = _maxPeriod, _minPeriod, _minPeriod & " to " & _maxPeriod )

[arr2025]

Hi @danextian 

Thanks for the response.

I tried recreating your scenario, but my slicer header is not changing based on the slicer selection. Here is the measure I used:

Periods Selected =

VAR _min =

    MINX ( ALLSELECTED ( CalendarEvent[StartDate] ), [StartDate] )

VAR _max =

    MAXX ( ALLSELECTED (  CalendarEvent[StartDate] ), [StartDate] )

VAR _minPeriod =

    FORMAT ( _min, "mmm yyyy" )

VAR _maxPeriod =

    FORMAT ( _max, "mmm yyyy" )

RETURN

    "Periods selected: "

        & IF ( _minPeriod = _maxPeriod, _minPeriod, _minPeriod & " to " & _maxPeriod )

Here is what my slicer shows:

It's showing all the dates instead of the selected range.

Note: I am using the same column referenced in the measure.

Could you please help me understand if I’m missing something?

[v-prasare · Accepted solution]

Hi @arr2025,

The issue is not with the DAX measure. The reason the title doesn’t update to reflect the selected month range is because the slicer is currently operating in continuous mode at the date level. In continuous mode: ALLSELECTED() returns the entire numeric range of date, evaluate the full range available in the slicer, rather than just the range selected by the user

As a result, the measure always shows the full minimum and maximum dates (e.g., 1984 to 2026), even when the user drags a smaller section on the slider.

To resolve this, the slicer needs to be changed to categorical mode so it works with discrete month values instead of a continuous date range.

Thanks,

Prashanth

[Ahmed-Elfeel]

Hi @arr2025,

It looks the Between slicer is treating your MonthStart column as a continuous date range

Ok here is sort of Approaches you can try:

First Approach : Use List View with Month Year Display Column

• So Create this in your date table:

MonthStart = DATE(YEAR([Date]), MONTH([Date]), 1)
MonthYearDisplay = FORMAT([MonthStart], "MMM YYYY")
SortOrder = YEAR([Date]) * 100 + MONTH([Date])

•  Then
  • Use MonthYearDisplay in the slicer
  • Set slicer to List mode (not Between or Dropdown)
  • Make sure MonthYearDisplay is sorted by SortOrder
  • Users can Shift+click to select a range of months

Second Approach : (This is the easiest)

• Go to AppSource in Power BI
• Search for Timeline Slicer by Microsoft
• Add it to your report
• Configure it to show months/years
• Users can drag to select month ranges easily

Let me know if this works☺️❤️

[Ahmed-Elfeel]

Hi @arr2025,

I hope you are doing well today☺️❤️

First Approach

• You can create a separate date table specifically for monthly filtering:

MonthsTable = 
ADDCOLUMNS(
    SUMMARIZE(
        CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
        'Date'[Year],
        'Date'[MonthNo],
        'Date'[MonthYear]
    ),
    "MonthStart", DATE([Year], [MonthNo], 1),
    "MonthYearDisplay", FORMAT(DATE([Year], [MonthNo], 1), "MMM YYYY")
)

Second Approach

• You can Use a Calculated Column for Month Start (add this to your table)

MonthStart = EOMONTH([Date], -1) + 1
// or
MonthStart = DATE(YEAR([Date]), MONTH([Date]), 1)

Moving to next step in your slicer:

• Use the MonthStart column
• Set slicer to Between mode
• Format as MMM/YYYY in the column formatting

Third Approach

• Create two separate slicers:
  • Year slicer (numeric column)

  • Month slicer (text: Jan, Feb, Mar...etc)

This should gives users clear monthly selection without date complexities

Bonus Solution

• Create Custom Column with Sorting:

MonthYearSort = YEAR([Date]) * 100 + MONTH([Date])
MonthYearDisplay = FORMAT([Date], "MMM YYYY")

• Then:

  • Use MonthYearDisplay in slicer
  • Sort by MonthYearSort
  • Set slicer to List mode instead of dropdown

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.