cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aashton
Frequent Visitor

Display start and end dates chosen in date slicer

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.
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @aashton ,

 

Sample data is this.

6.png

 

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?

5.png

 

Here's the workaround.

1.Create a calendar table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

7.png

 

2.Create a relationship between two tables.

8.png

 

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'.

9.png

 

 

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.

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @aashton ,

 

Sample data is this.

6.png

 

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?

5.png

 

Here's the workaround.

1.Create a calendar table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

7.png

 

2.Create a relationship between two tables.

8.png

 

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'.

9.png

 

 

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.

View solution in original post

MFelix
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



aashton
Frequent Visitor

I 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



aashton
Frequent Visitor

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?

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.