How to limit the values of Calendar[Date] relevant to the values of the related date column?

[olimilo]

I have 3 different tables, one of each representing a different department. The date ranges (ie: start/end dates) and date measures (measure might be the wrong word to describe it, but it should be booking date, created date, testing date, reporting date) differ between each department. These tables are connected to a Calendar table ranging from 2021 to 2030.

For example, DeptA[BookingDate] ranges from 2024 onwards, DeptB[ReportingDate] from 2025 onwards, etc. Both those dates are related to the Calendar[Date] column.

Using the Calendar[Date] as the slicer value for each department's tab, by design the slicer will show the dates from 2021 to 2030. Obviously, this will confuse the user because it is showing all of the dates including those that are irrelevant to the actual dataset.

I cannot use the actual date column for slicers and charts because its date hierarchy is lost the moment I related it to the Calendar table.

My question is, is there a way I can limit the values of the Calendar[Date] value's date hierarchy to show only the dates relevant to the content I am trying to show? If I wanted to create a slicer for the DeptA[BookingDate], can I make the slicer with the Calendar[Date] value show only the dates of the DeptA[BookingDate] column?

[Ashish_Mathur · Accepted solution]

Hi,

Create this simple measure

Total = sum(Data[Sales])

Select the Date slicer, expand the filter pane and drag this measure to the section of "Filter on this visual".  Set the condition to Not blank.  Click on OK.

[v-pgoloju]

Hi @olimilo,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @olimilo,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Ashish_Mathur , @mnadeemsalam , @Zanqueta  and @xifeng_L  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Ashish_Mathur · Accepted solution]

Hi,

Create this simple measure

Total = sum(Data[Sales])

Select the Date slicer, expand the filter pane and drag this measure to the section of "Filter on this visual".  Set the condition to Not blank.  Click on OK.

[olimilo]

There's also a chance this will show a "broken" calendar, right? Like if there is no data to be shown for Jan, May - those periods would not be available in the filter selection, correct?

[Ashish_Mathur · Accepted solution]

The months should show up because that field has been dragged from the Calendar table.

[mnadeemsalam]

Hi @olimilo ,

Yes — you can dynamically limit the Calendar slicer so that it only shows the dates relevant to each department’s date column.

Based on your requirement I can suggest you 2 possible solutions:
Solution 1 — Use a "Date Range Filter Measure" + Visual-Level Filter

This method keeps your Calendar table fully functional (hierarchy intact) AND restricts the slicer to relevant dates per report page.

You create a measure that evaluates whether each Calendar row is “within" the active fact table’s date range.

Then you apply that measure as a filter on the slicer visual (not on the model).

Let me guide you step by step how to achieve this:
Step 1: Create a measure for DeptA

Example for DeptA[BookingDate]:

DeptA Date Filter =
VAR MinDate =
    CALCULATE ( MIN ( DeptA[BookingDate] ), ALL ( DeptA ) )
VAR MaxDate =
    CALCULATE ( MAX ( DeptA[BookingDate] ), ALL ( DeptA ) )
RETURN
IF ( SELECTEDVALUE ( Calendar[Date] ) >= MinDate &&
     SELECTEDVALUE ( Calendar[Date] ) <= MaxDate,
     1,
     0
)

Step 2: Add Calendar[Date] to slicer

Keep using Calendar table — not DeptA’s date column.

Step 3: Apply visual-level filter

Select the slicer → Filters → Add DeptA Date Filter → set to is 1

Now the slicer will show only the Calendar dates that exist in DeptA's BookingDate range.

Effects:

• Calendar hierarchy stays fully functional
• Slicer hides irrelevant dates automatically
• No model changes required
• Different pages can use different measures for different departments

Solution 2 — Create separate “Date Active” flags in Calendar (using calculated column)

This modifies the Calendar table itself so it knows which dates are relevant to each department.

Here is example code for calculated column: 

DeptA Active Date =
VAR HasData =
    CALCULATE (
        COUNTROWS ( DeptA ),
        DeptA[BookingDate] = Calendar[Date]
    )
RETURN IF ( HasData > 0, 1, 0 )

Then filter the slicer with Calendar[DeptA Active Date] = 1.

Pros

• Easy
• Auto-limits slicer based on actual data

Cons

• Cannot use direct inactive relationships
• Calendar gets cluttered with per-department columns
• Harder to maintain long-term

Solution 3 — Having separate calendar table for each fact table, which I won't recommend you.

Since you have multiple departments with different date ranges and want to keep the Calendar hierarchy, the dynamic measure filter approach (Solution 1) is the best.

Use:

• DeptA Date Filter on DeptA page
• DeptB Date Filter on DeptB page
• DeptC Date Filter on DeptC page

Each slicer will show only the relevant date range from the central Calendar table.

If this response helped resolve your issue, please consider marking it as the accepted solution.
Your feedback is appreciated — a Kudos is always welcome!

[Zanqueta]

Hi @olimilo,

This is a common modelling challenge in Power BI when using a single Calendar table with multiple fact tables that have different date ranges. By design, the Calendar table will always show its full range (2021–2030), even if the related fact table only has data from 2024 onwards. There is no native feature to dynamically trim the Calendar slicer based on the selected department, but there are two practical approaches:

My suggestion:

Create separate Calendar tables for each date role (Booking, Reporting, Testing).

For example:

• Calendar_Booking → linked to DeptA[BookingDate]
• Calendar_Reporting → linked to DeptB[ReportingDate]

Use the appropriate Calendar table as the slicer for each page.
Pros: Simple, no DAX complexity, slicer automatically shows only relevant dates.
Cons: Adds extra tables to the model.

Why this is my preferred:

• It is cleaner and avoids complex filtering logic.
• It scales better for multiple date roles.
• It preserves the date hierarchy without additional measures.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[xifeng_L]

Hi @olimilo 

You can use measure in the slicer's filter panel to control which dates should be displayed, such as :

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[grazitti_sapna]

Hi @olimilo,

You can achieve it by following below steps:

• Go to DeptA page

• Select the slicer that uses Calendar[Date]

• In the filters pane, expand the slicer

• Drag DeptA[BookingDate] into the slicer’s filter pane

• Set filter to: is not blank

Else you can create a measure like this 

Date Visible DeptA =
IF (
CALCULATE ( COUNTROWS ( DeptA ), ALLSELECTED ( Calendar ) ) > 0,
1,
0
)

and use it on filter pane set it to 1

If you want to add all three, you create a measure using switch function with selected value 

alternatively:

You can add these filters to calender slicer

DeptA Page

• Slicer: Calendar[Date]

• Filter: DeptA[BookingDate] is not blank

DeptB Page

• Slicer: Calendar[Date]

• Filter: DeptB[ReportingDate] is not blank

DeptC Page

• Slicer: Calendar[Date]

• Filter: DeptC[TestingDate] is not blank
  
  🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
  💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
  🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
  🔗 Curious to explore more? [Discover here].
  Let’s keep building smarter solutions together!

[rohit1991]

Hii @olimilo 

Power BI will always show the full range of your Calendar table, so you cannot limit the slicer to only the dates that exist in each department’s fact table. A shared Calendar cannot shrink its values per table. The only working solution is to create a separate date table for each department and use that table in the slicer then the slicer will automatically show only the relevant dates.