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 created a series of conditions in a new column, used to filter customers, but they are built using a start and end date. At the moment I have the [start date] and [end date] created as specific DATE( ) formulas.
This works like I need but it will require me to go in an manually change the DATE( ) formulas for viewers whever they need a different date range. Is there a way to create a field, measure, or column that users can change the dates themselves and still allow me to use it in my formulas?
e.g. start date= DATE( 05/01/2022)
end date = DATE(05/01/2023)
served this year = IF( [service date] >= [start date] && [service date] <= [end date], "served this year", "not served this year")
Solved! Go to Solution.
You should be able to use the slicer to achieve this. Select the slicer visual and add your date field/column as the field
Then go to format visual>slicer settings and change the style to "Between"
The users can either type in the date they want or use the slider and it'll adjust the date range.
You should be able to use the slicer to achieve this. Select the slicer visual and add your date field/column as the field
Then go to format visual>slicer settings and change the style to "Between"
The users can either type in the date they want or use the slider and it'll adjust the date range.
That works for my first condition. I started with the between two dates field, the other conditions are [applied for program] before the [end date] and [service stopped] after [start date]. The slicer is great for looking between two dates but if I want to use the dates in a >= and a <= condition, I don't think I can use a slicer in a formula.
You could also add a date to your filters pane and set it to Advanced filtering. As long as you don't lock the filter the user can adjust it as needed. They can also change the filter type, but the advanced date filter will allow them to see before, after, or between dates
I tried that in my first draft and that worked but it was inefficient for my viewers. It meant that viewers would have to change the [start date] and [end date] for every Viz on every sheet due to some tables using the same fields differently (rather than applying the filters to the entire sheet or all sheets).
On my second attempt, I built my formulas using the same two DATE( ) formulas so when those two are edited all of my tables on all of my sheets automatically upate. The goal was just not to need me to go in and change the dates for every viewer if they need to consider different dates. If they can enter their own dates the tables will all automatically reflect what they are after.
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |