Date Slicer not working properly

[ChrisR22]

Hello,

I have several query data sets that each have date columns (dates that tasks were complete). I have created a calendar query (that lists out every date between 2021 and 2024) and established many-to-one relationships with each of these dates to this date field.

For some reason, when I attempt to create a slicer using that calendar date field, it eliminates most of my rows.

For more context, the dates fields in each of my datasets originated as date/time fields (with a handful of rows that only showed date). When the slicer started filtering out everything except those handful, I had made duplicate columns in each table that used the same data only using the 'date' data type instead of 'date/time', assuming that he data type mismatch had been the cause. I thought this would eliminate my issues but the slicer still seems to be eliminating every row except for those handful that had contained a date and time in the original date/time column, even though the relationship isn't connected to that column (the relationship is using the duplicate field that had the 'date' data type).

Are there nuances to be aware of when using a date slicer on date/time fields?

Even thought that is not my case, it might be playing a role.

Regardless, it still doesn't explain why the slicer, when engaged, immediately eliminates data that is well within the slicer between ranges. I have also tried dropdown and every other slicer variation and still no solution.

Any insight or assistance would be greatly appreciated!

[v-rongtiep-msft · Accepted solution]

Hi @ChrisR22 ,

There are a few possible reasons why your date slicer is not working as expected. Here are some things you can check and try to fix the issue:

Make sure that the data type of your date fields in your query data sets and your calendar query are consistent. You can use the Power Query Editor to change the data type of your columns to Date or Date/Time as needed.
Make sure that the relationships between your query data sets and your calendar query are valid and active. You can use the Manage Relationships dialog box to view and edit the relationships in your model. You can also use the Model view to see the cardinality and direction of the relationships.
Make sure that the date slicer is using the correct field from your calendar query as the Field value. You can also try to change the slicer type from Between to List or Dropdown to see if that makes any difference.
Make sure that there are no other filters or slicers on your report page that might be affecting the data displayed by the date slicer. You can use the Filters pane to see and modify the filters applied to your visuals.

Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

https://zebrabi.com/guide/how-to-add-date-slicer-in-power-bi/

https://www.spguides.com/power-bi-date-slicer/

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ChrisR22 , I doubt your date in the table has a timestamp, which is not visible, because of data type time.

IF change  datatype to datetime and use format to see time, you should be able to see timestamp other than 12:00 AM

If so then create a date column

Date = Datevalue([Datetime column])

And join this.

Another reason could be less dates in calendar/date table

Why Time Intelligence Fails - Powerbi 6 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ChrisR22]

@amitchandak thank you for responding. 

I am a bit new to PowerBI, could you explain in a little more detail what you mean?

Which column am I changing to datetime? 

a. the 'date' column in the Calendar table that is being used in the relationship

b. the original datetime column in each of the queries

c. the duplicated column that has the original data but formatted to date instead of the original datetime

And what doe you mean by "use format to see time"?

Am I creating that date column, using your formula, in the calendar table or in each of the queries?

Thanks again so much for your assistance!