Best way to implement a date time slicer?

[pbi-ai]

For datasets where I have a date time field in my dataset, what is the best way to accomplish the time granularity via slicer?

I've currently got reports with datasets that either have a date column or a date time column. My datasets come from views from an Azure SQL database.

Normally I drag the date/datetime column onto the report and select the slicer visual which results in a date slicer with a slider. The reason I'm going with a slicer is we don't use filters and hide the filters pane and allow all the interactivity on the report page itself via slicers. I have seen the Relative Time option but this wouldn't work in my scenario.

An example of a use case of a datetime slicer would be to select from 4 AM today to 2 AM tomorrow. This is considered one business day for a client of ours so it is necessary to allow that datetime slicer granularity. Without datetime slicer, they would simply get data for 2 whole days using the date slicer.

[vojtechsima]

Hi, @pbi-ai,
I believe you can create a Date Table and an Hour Table. Split the fact DateTime column into Date and Time and map it with those new tables accordingly. Then you will create two slicers, one for date and one for hours and you should be set for success.

[pbi-ai]

@vojtechsima - So there would be no way to have a single date time slicer? It would have to be two different slicers, one controlling date and one controlling time?

Assuming someone selects the start date to be 2022-03-01 and end date to be 2022-03-02...and start time to be 4 AM (for 3/1) and end time to be 2 AM (for 3/2), since 2 AM end time is smaller value than start time ( in the scope of the time slicer)...will it still filter correctly?

[vojtechsima]

Hi, @pbi-ai,
Here's an interesting topic on a different site that deals with your question:
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=904b228b-...

Quoting from that answer: 
"It would be nice to have both the date and time on the same slicer, however, it would create a great deal of unnecessary bulk in your data model.

One day would have 1440 minutes and multiply that by the number of days in your calendar. A year's worth of calendar would result in over 500,000​ rows.

So an optimal way to go is to separate the date and the time in 2 dimension tables and 2 different columns in your fact table."

Also, it's technically impossible to create one slicer with Date and Time, as PowerBI doesn't support it, it only allows you to choose Date when you put the DateTime column into a slicer.

See here (The only option to slice a time is a relative time when the DateTime column is given) 

[pbi-ai · Accepted solution]

@vojtechsima - Thanks for that.

I was able to create a separate date and time slicer as a test. However, the time slicer won't work as intended in my scenario of showing data from 4 AM the previous day to 2 AM current day. The time slicer works great if it's for a single day or if you need to look for the same time every day. But when selecting two days and trying to see parts of both days, this does not accomplish that.

What I had to do to make this work (it's not an elegant solution) was to convert datetime into yyyyMMddhhmmss, so basically datetime to int. Use that field with a slicer and now with this you have much more precision into what you want to see and how you want to see it over various days.