Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
user_guddu10
Advocate I
Advocate I

Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

Hello Power BI Community,

I am working on a report in Power BI where I need to use a slicer to filter data based on overlapping time frames: "Last 12 Months," "Last 24 Months," and "Last 36 Months." I want each time frame to include all the data up to the present, with each range overlapping the next (e.g., "Last 24 Months" includes "Last 12 Months").

Setup Details

I created a separate DateTable specifically for managing date-related calculations. Here’s how I structured this table:

Creation of DateTable:

  • I generated the DateTable using the DAX function CALENDAR, to cover a range from the earliest date needed for my reports up to the current date.

 

DateTable = CALENDAR(MIN('MainDataTable'[Date]), TODAY())

 


- This ensures the DateTable is always up to date and covers all possible dates in my data model.

DAX Formula for Time Frames:

  • I added the following DAX formula to categorize each date into the specified overlapping time frames:

 

 

Time Frame = 
SWITCH(
    TRUE(),
    DateTable[Date] > EDATE(TODAY(), -12), "Last 12 Months",
    DateTable[Date] > EDATE(TODAY(), -24), "Last 24 Months",
    DateTable[Date] > EDATE(TODAY(), -36), "Last 36 Months",
    "Older"
)

 

 

Issue: When I use this formula for a slicer in my report, it does not behave as expected. The slicer treats each time frame independently rather than recognizing the overlap between them.

Example of the Issue:

  • Current Date: January 28, 2025
  • Expected Behavior: Selecting "Last 24 Months" should show records from January 28, 2023, to January 28, 2025, inclusive of the "Last 12 Months."
  • Actual Behavior: Records from January 28, 2024, to January 28, 2025, appear to be excluded when "Last 24 Months" is selected, indicating a possible filtering issue.

 

My questions are:

  • Is there an error in how I've structured the DAX formula that might be causing this issue?
  • How can I modify the formula or the slicer settings so that the slicer correctly accounts for overlapping periods?

Any guidance or suggestions would be greatly appreciated as this functionality is crucial for the analytical capabilities of my report.

Thank you!

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@user_guddu10 

Based on your requirment, it is not possible to include a single date to be in two different periods ( example:  12M and 24M), you will have to have separate set for each period and stack them, please follow this video, it will help you: Custom Date Period Selections in Power BI - YouTube



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
user_guddu10
Advocate I
Advocate I

Thanks a lot! This solved my issue!

Fowmy
Super User
Super User

@user_guddu10 

Based on your requirment, it is not possible to include a single date to be in two different periods ( example:  12M and 24M), you will have to have separate set for each period and stack them, please follow this video, it will help you: Custom Date Period Selections in Power BI - YouTube



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.