Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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").
I created a separate DateTable specifically for managing date-related calculations. Here’s how I structured this table:
Creation of DateTable:
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:
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.
My questions are:
Any guidance or suggestions would be greatly appreciated as this functionality is crucial for the analytical capabilities of my report.
Thank you!
Solved! Go to Solution.
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks a lot! This solved my issue!
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |