Filter is displaying all months even tho for that year there isn't data for those months

[Theitalianpeter]

Hello all,
I have come into a bit of a pickle, i have two slicers, one for years and one for months, the one for years is from 2013 to 2015, except for 2015 which has only records up to May, all the others have data, what i was wondering is that if anyone knows if its possible to add a filter or a dax to the Filter by Year slicer, so that it would restrict the months filter when the year is set to 2015.

If anyone knows how to do it i would really appreciate it, and if not, thank you still for your time and patience.

Best Regards,

Peter.

[Theitalianpeter]

First of, good morning! And thank you for your help.

I tried your solution and it worked really well so thank you very much, i messed around for a while and another way that i found, is to use the calendar date to filter the month slicer in the filters, by doing an advanced filter, when the value "is before" the date selected that enables me to filter the months by year as desired too.

[miTutorials]

You are hardcoding the Date and will have to change manually everytime. Not ideal to do this. There is another workaround check out the below video.

Months in Slicer Appearing when you don't have any data ? PowerBI Tutorial (youtube.com)

[Theitalianpeter]

Thank you, will make sure to see it and try its solution.

[Shravan133 · Accepted solution]

Steps to Restrict Months Based on Selected Year

1. Create a Calendar Table:

   • First, ensure you have a calendar table with all dates, years, and months. This table helps with filtering and is often used in Power BI reports.
   Calendar = CALENDAR(DATE(2013, 1, 1), DATE(2015, 12, 31))
   • Add calculated columns for Year and Month:
   Year = YEAR([Date]) Month = FORMAT([Date], "MMMM")

2. Create Relationships:

   • Ensure you have a relationship between your data table and the Calendar table using the Date field.

3. Create a Measure to Filter Months:

   • Create a measure to dynamically filter the months slicer based on the selected year.
   FilteredMonths = VAR SelectedYear = SELECTEDVALUE(Calendar[Year]) RETURN IF( SelectedYear = 2015, CALCULATE(MAX(Calendar[Month]), Calendar[Year] = SelectedYear, Calendar[Date] <= DATE(2015, 5, 31)), CALCULATE(MAX(Calendar[Month]), Calendar[Year] = SelectedYear) )

4. Use a Slicer for Months:

   • Add a slicer for months using the Month field from your Calendar table.

5. Apply Conditional Formatting:

   • You need to ensure that the months slicer only shows the relevant months based on the selected year. You can use a measure in visual-level filters to hide months that should not be available when 2015 is selected.

6. Create a Measure for Visibility:

   • Create a measure that you can use to conditionally show or hide the months based on the year selection.
   ShowMonth = VAR SelectedYear = SELECTEDVALUE(Calendar[Year]) RETURN IF( SelectedYear = 2015, IF(Calendar[Date] <= DATE(2015, 5, 31), 1, 0), 1 )

7. Filter the Month Slicer:

   • Go to the Visualizations pane, select the months slicer, and apply a filter using the ShowMonth measure.
   • Set the filter to show values where ShowMonth equals 1.