Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

user_guddu10 — Tue, 28 Jan 2025 06:18:24 GMT

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!

Re: Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

Fowmy — Tue, 28 Jan 2025 06:50:06 GMT

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

Re: Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

user_guddu10 — Wed, 29 Jan 2025 01:14:39 GMT

Thanks a lot! This solved my issue!

topic Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly in DAX Commands and Tips

Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

Setup Details

Example of the Issue:

Re: Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly

Re: Issues with Overlapping Date Ranges in Slicer Not Displaying Correctly