Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
User | Count |
---|---|
17 | |
16 | |
14 | |
13 | |
12 |
User | Count |
---|---|
17 | |
14 | |
12 | |
10 | |
8 |