Slicer on DateTime field

[jitpbi]

Hi,

Is there any workaround possible to use both date and time when the type of slicer is Between?

Using visual/page level filter or filter at service is not preferable at all.

Thanks

[Sumanth_23]

hi @jitpbi - I am not too clear on the request - can you provide a screenshot on what your data looks like and what is the expected output? thanks! 

[jitpbi]

Hi @Sumanth_23 ,

Please refer the below screnshot where first 2 slicers are "Dropdown" and "List" type while the third one is type of "Between" 

the same field (Datetime) is used for all three slicers above but time doesn't come in the third slicer. I want the time should also there along with the date in third type of slicer. 

Thanks

[Sumanth_23]

hi @jitpbi - The between slicer is not enabled to work with times - if you are using date time in slicers then you can only use the list and dropdown option. You can refer to the below link for a similar question: 

https://community.powerbi.com/t5/Desktop/Slicer-Date-and-Time/td-p/120431/page/2

The between slicer gives the user an option to use a date picker and hence designed to work with just dates even though you are adding a datetime column to the slicer. 

Unfortunately the only option with regard to Time is the "Relative Time" option; but not sure if that will serve your requirement

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[jitpbi]

Hi @Sumanth_23 ,

I tried by extracting "Hour" from the datetime field and used as another slicer but still some issue when need to filter something like in between (13:30 to 14:30) and also don't want to use 3 different filters for date, hour and minutes.

Is there any way if minutes can also come along with hours and then use "HourMinute" column as a slicer of type Between?

[Sumanth_23 · Accepted solution]

hi @jitpbi - You can try  the below approach

1. I extracted the minutes from the Date time field 

Mins = MINUTE('Z-DateTime-Sort'[DateTime])

2. Created a Hour-Mins column

Hour-Mins = 'Z-DateTime-Sort'[Hour] + DIVIDE('Z-DateTime-Sort'[Mins], 60)

 

3. Use the Hour-Mins column in the slicer - it would display time as "hh:mm" but more in the format displayed below

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!