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.
Hello,
I have a filter on date of service where the user can choose a date range to view records. I'd like to display the date range chosen at the top of the report.
Solved! Go to Solution.
Hi @aashton ,
Sample data is this.
Do you want to have a slicer to put the date, and then after the date is filtered, even if there is no value in the corresponding date, the date will be displayed?
Here's the workaround.
1.Create a calendar table.
Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
2.Create a relationship between two tables.
3.Change the date column of the slicer to the date column in the calendar table. Similarly, the date column in the table is also changed to the date column in the calendar table. And tick ‘Show items with no data'.
You can more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aashton ,
Sample data is this.
Do you want to have a slicer to put the date, and then after the date is filtered, even if there is no value in the corresponding date, the date will be displayed?
Here's the workaround.
1.Create a calendar table.
Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
2.Create a relationship between two tables.
3.Change the date column of the slicer to the date column in the calendar table. Similarly, the date column in the table is also changed to the date column in the calendar table. And tick ‘Show items with no data'.
You can more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aashton
Are you using a calendar table or the values are taken directly from your fact table? If you are takinng the values from a table that does not have the values the maximum value that will be picked up will be the one on your table.
Power BI will create a date table from any date field however if the values aren't part of the table they will not appear.
In the case of dates you are advised to create date table with continuous date and used related with your fact table that way you can use it on a slicer and then using the MAX and MIN function you will get the values you are searching for in this case first and last day of January.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI am pulling the date of service directly from a Charges table, not a date table.
Hi @aashton
To what I can understand from your sentance you don't have all the dates values on that Charges table correct?
The "automatic" date calendar that is created when you have a date field is a virtual one so you won't be abble to check the data that isn't there.
You need to create a date table and make a relationship wiht that charges table then use it on your slicer and corresponding measure to get maximum and minimum value.
See below two options to create your calendar table
https://powerbi.tips/2017/11/creating-a-dax-calendar/
https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSo I've added the date table and joined the Date.date to Charges.DOS. I add a date slicer on Date.Date, and choose a range. If I then use MIN(date.date) it gives me the oldest date in the date table, not the starting date they chose on the date slicer?
Hi @aashton what do you mean by join? Do you mean a relationship?
And are you creating a measure or a calculated column?
The Min function should be used in a measure
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |