Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a data set in Power BI where there's a date column. I'd like to filter the dates along the following criteria: past 30 days, past 60 days, past 90 days, and all time.
I added three custom columns, each with binary values: `is_past_30`, `is_past_60`, and `is_past_90`. I then created a calculation called `Time Frame` and set it to the following:
Time Frame = IF( Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 1, "4. All time" , IF( Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 2, "3. Past 90 Days" , IF( Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 3, "2. Past 60 Days" , IF( Query1[is_past_30]+Query1[is_past_60]+Query1[is_past_90] < 4, "1. Past 30 Days" , "n/a" ) ) ) )
This code works if I want to slice the time periods exclusively- i.e., past 60 days but NOT in the past 30 days. However, by "Last 60 Days" I'd like to include the last 60, too. Under this scheme, probably because of the "IF-THEN" statement, that is not possible when placing the options in a slicer.
Does anyone have a suggestion for a workaround?
Solved! Go to Solution.
Hi @andres6and8
Here is one option that uses Measure tables. The link to a PBIX file is here
https://1drv.ms/u/s!AtDlC2rep7a-jlM8zvxEyg6XK2MB
Bascially you can add your own date ranges to the table called Date Ranges and these will automatically appear in your slicers.
Use the tab called All Sorts to see what I mean.
In @Phil_Seamark's pbix, it contains a "Days From Today" column.
This columns is used to compare with "days" (which is ID in Date Range table) for calculation.
You can also refer to blog below for this kind of "Last X period" requirement:
Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..
Regards,
Hi @andres6and8
Here is one option that uses Measure tables. The link to a PBIX file is here
https://1drv.ms/u/s!AtDlC2rep7a-jlM8zvxEyg6XK2MB
Bascially you can add your own date ranges to the table called Date Ranges and these will automatically appear in your slicers.
Use the tab called All Sorts to see what I mean.
Thank you for the example. This is definitely what I'd like to implement, but beside creating a reference table with ID and Time Frame, I'm really not sure how to implement everything else. Could you provide a little more explanation?
Thank you in advance!
In @Phil_Seamark's pbix, it contains a "Days From Today" column.
This columns is used to compare with "days" (which is ID in Date Range table) for calculation.
You can also refer to blog below for this kind of "Last X period" requirement:
Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |