Display start and end dates chosen in date slicer

[aashton]

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.  

For example, I choose Jan 1 2021 - Jan 31 2021, I want these dates displayed, even if the records do not exist for this entire date range.

[Anonymous · Accepted 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.

[MFelix]

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.

[aashton]

I am pulling the date of service directly from a Charges table, not a date table.

[MFelix]

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/

[aashton]

So 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?

[MFelix]

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