Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Time of day slicer

I can't seem to figure out how to have a time of day slicer that lets me use the between style slice and/or a relative time based on minutes/hours.  The slicer converts my time of day numbers into dates even though the column type is set to time - 12/30/1899 to be exact!  Anyone else figured out why it does this or if any other store tools allow granular filtering based on time of day (not just by hour?)

 

Thanks!

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may add multiple slicers to set time value by Using What if parameters, and then refer to this discussion.

Community Support Team _ Sam Zha
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

16 REPLIES 16
johnt75
Super User
Super User

For anyone else finding this through Google, I managed to get the behaviour I was looking for by creating a new column

HourForSlicer = HOUR([date_field])

 

You can then create a slicer based on the date and another slicer based on the hour. Not perfect but gets the job done

pabeader
Post Patron
Post Patron

There is currently no way to create a time based slicer in Power BI.  It is possible in Excel but not in Power BI.

Guy in a Cube Patrick did a quick video about what can be done.  You can do a List or a dropdown just not Between or other things like that.

 

Guess it shouldn't be called Time intelligence.  Maybe Date intelligence would be more correct.

 

 

DesaiH
Frequent Visitor

did anyone find the solution for this slicer issue?

I also have date and time column seperatly. Based on date selection i want to select my time range but when i have slicer for time, it convert into date 12/30/1899. 

any work around?

 

Anonymous
Not applicable

I would also like to see the ability to slice by time as well as date. I have large date/time stamped data tables which build over 24 hours every day. I need to be able to slice it between 1700 on one day and 1700 on a subsequent day [other random times are also required] and a slider would be the most obviously user-friendly way of accomplishing this. The same goes for Filters, which can't deal with times at all other than manually selecting the required minutes in a Basic filter, which is not practicable for a 15hour sample.

 

I have built a crude slicer value using

slicervalue = hour([datetime])&format(minute([datetime]),"00") formatted as whole number

 

This gives a value between 0 and 2359 for all times, which will then act as a sliding slicer, but it still won't work over different dates.

If the Date slider variant of slicer can convert date values into a slider, then surely it can't be beyond the wit of man to do the same with the decimal time values? Not all of us are looking at data over fanancial years. Some of us are working the fine details.

 

Come on Microsoft. Make us happy.

urnaim
New Member

I am also struggling with that issue - has anyone found a solution yet? there has to be a way!

 

Thanks in advance!

v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may add multiple slicers to set time value by Using What if parameters, and then refer to this discussion.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Sam, Phil, thanks guys.  It's not clear to me how Sam's referenced discussion answers the op's question.  After reading said discussion I remain mystified.  Apologies!

 

Here's what I need: a way to allow the user to select either a single TimeOfDay at Hour and/or Minute and/or Second granularity, or a range of TimeOfDay, either before or after selecting a range of Dates at Date granularity.  The goal is to allow focusing on data for an arbitrary continuous range of Times within a Date, across a range of Dates.  I hope that makes sense.

 

Separating Date and Time and allowing the user to select each as needed can be a powerfull analytical tool.  We need it.  Our data are timestamped to the second.

 

Currently when a Time (SQL time(7), Power BI Time) is put in a Date slicer, the only value textually displayed is the "beginning of time" value 12/30/1899.  This makes perfect sense, but is not useful in our scenario.

 

Will continue to research how to meet this requirement, and will post any useful results here.  Can anyone point me to helpful resources?  Thanks in advance. 

@MikeDonnellan Hi Mike, similiar to you I am currently working on data where I want to filter out by a set date period, followed by time of day as an adjustable scrolling splicer (where the data type of the hours are still 'time'). Were you able to resolve your issue in a different way to the above solution? Thanks!

Hi @5hae

 

You may try this PBIX which contains a table with date and time values and some randomly generated numbers in Power Query and wherein the data time data columns are used in a slicer.

 

https://drive.google.com/open?id=1muN42p5vxZhRWo70sGzKEF8VgAnCZRli






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

I never found quite what I was looking for.  Seems simple to just add an hour/minute date filter to the slicer so you can get as granular as you want/need.

I am also struggling with this feature.  Is there a solution out there?  all I want to do is use a slicer between two dates with a specific time.  ex. 1-jul-2018 6:30am to 18-jul-2018 6:30am.

 

Is there a solution to this?

I am also struggling with this issue. When I put a slicer on datetime feild it only picks date and ignore time. I want filtering on Time as well. Any idea will be appreciated. Thanks..

Has anyone found a solution to this issue?  I also want to narrow my search using a slicer between two dates at specific time periods.  ex. 1-Jul-2018 6:30am to 10-Jul-2018 6:30am

 

Any help would be amazing.  This has me perplexed!

It is not at all clear to me how this answer solves the original request.  Apologies!  Even after reading the referenced discussion I remain mystified.

 

Here is what I need: a way to be able to select either a single time value or a range of time values from a TimeOfDay table, using a slicer with a slider.

 

For example, I have separate Date and TimeOfDay tables.  The TimeOfDay table has 86,400 rows, one for each second of the day.  It also has columns for Hour and Minute (just integer indexes). 

 

The desired behavior is to allow the user to select either a single date or a range of dates in a Date slicer with slider, then select either a single time value or a range of time values in a a Time slicer with slider, and see the data filtered appropriately in other visuals.  By separating Date and TimeOfDay, in-day patterns might be easily distinguished in the data within a range of Dates.

 

Even Hour granularity might be acceptable, but being able to dynamically switch among Hour, Minute, and Second would be ideal.  Ask for the moon, you might get it :-).

 

As the OP notes, when a TimeOfDay value (represented as data type time(7) in SQL Server and of data type Time in Power BI) is shown in a Date slicer, the only thing shown textually in the slicer is the "beginning of time" date December 30, 1899.  This makes perfect sense, but is not useful in my scenario.

 

Will continue to research this question and will post any useful results as they are found.  Can anyone point me to a resource that can help me deliver the desired behavior?  Thanks in advance.

Phil_Seamark
Employee
Employee

Hi @Anonymous

 

You can probably achieve this through the use of a couple of disconnected tables.  One for range and the other for time.

 

If you give me a bit more detail on how you would like to slice your data then post here and I'll try and mock up a simple model for you.

 

Phil


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

I have a separate time and separate date column.  I tried creating a new combined column hoping that would help, but the slicer visual only shows a date.  I want to look at specific times like 8:30AM-9:30AM for specific records entered.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.