Forum Discussion

jitpbi's avatar
jitpbi
Icon for Post Patron rankPost Patron
5 years ago
Solved

Slicer on DateTime field

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
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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!

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
jitpbi
Icon for Post Patron rankPost Patron
5 years ago

Hi Anonymous ,

 

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!