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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dbadmin
Helper IV
Helper IV

How to create a time range slicer?

I need to create shift reports in Power BI. However - I can't figure out a way to filter the page by shifts(times). 

 

For instance if I want to have two shifts

 

6am - 4:30pm

4:30pm - 3am (the next day) 

 

Is there a way to create this in Power BI - using DAX (which I'm not familar with at all)??

 

Or even create a dynamic time range slicer / fitler that allows me to select a start time and an end time? 

 

Any ideas, suggestions or points in the right direction - would be greatly appreciated! 🙂 

 

Thanks!

2 ACCEPTED SOLUTIONS
itchyeyeballs
Impactful Individual
Impactful Individual

First off (and you may already have this) It may be worth creating a shift lookup (dimension) table similar to a date table. This could include one record for every shift past and future and hold metadata such as start_date, end_date, start_time, end_time, shift_type (morning/evening), length, shift_number (unique integer starting at 1 to identify order of shifts), work_period(a group that sets of shifts belong to), supervisor etc.

 

Every shift in your data (fact) table would be referenced by a shift_id that links to the shift_number in the shift lookup (dimension) table. The shifts lookup would probably need to be manually created in Excel then imported with updates carried out as needed.

 

You could then just set up simple slicers to look at shift type or start time etc.

 

 As an extra option there is a custom visual (Advanced Timeline) that allows you to specify periods of time - https://app.powerbi.com/visuals/

but this will only work with start or end times, not both.

 

Creating a single slicer that dynamically includes all shifts that  have start and end times that intersect given bounds will be challenging, the logic of working out which shifts overlap a specified period is possible but you would have to work out how you were going to store the start and end poitns of your specified period. 

View solution in original post

greggyb
Resident Rockstar
Resident Rockstar

This is where you'll need to cover some of this in your data model. You should create a shift dimension that has unique keys for each unique shift. So you'll have one row with a primary key that represents the shift from 6am-4:30pm on 2016-01-08, and another row representing the shift from 4:30pm 2016-01-08 to 3am 2016-01-09, a third row representing the shift from 6am-4:30pm on 2016-01-10, and so on...

 

Otherwise your night shift will be really wonky. The first instinct would be to use a date dimension and a time dimension and set page filters on those. But then you'd run into the situation where you say Date = 2016-01-08 OR Date = 2016-01-09. Separately you'd set a filter on time where Time >= 4:30p AND Time <= 3:00a. Then you'd get 12:00a-3:00a on both days, and 4:30p-11:59:59p on both days.

 

The second instinct might be to store a full date-time field in your fact table, and then you can just filter the single field. This would be a bad idea from compression and memory usage standpoint. Filters would also be relatively slow on this field due to its high cardinality (lots of unique values to check). A workaround here would be truncating seconds, so you're only storing datetime down to the minute or 5 minute granularity.

Doing this would be relatively okay from a performance and memory perspective, but it's inconvenient from a UI perspective, because end users will need to know start and end times (including where these are different due to OT or scheduling changes) and will need to set both start and end as filter criteria.

 

With a shift dimension, you can easily capture OT and schedule changes in ETL, and to look at one shift or multiple shifts, you can make single selections for each. Moreover, you can select non-contiguous shifts. You can have attributes of the shift, such as 'Night' vs 'Day', or shift length, to capture OT when it happens. You can then do things like select all night shifts, which would be impossible with a date-time filter in PBI. Or you can divide output by the number of hours in the shift.

 

**Edit** @itchyeyeballs beat me to it. Hopefully hearing it from two people makes it sound like an even better idea.

View solution in original post

4 REPLIES 4
greggyb
Resident Rockstar
Resident Rockstar

This is where you'll need to cover some of this in your data model. You should create a shift dimension that has unique keys for each unique shift. So you'll have one row with a primary key that represents the shift from 6am-4:30pm on 2016-01-08, and another row representing the shift from 4:30pm 2016-01-08 to 3am 2016-01-09, a third row representing the shift from 6am-4:30pm on 2016-01-10, and so on...

 

Otherwise your night shift will be really wonky. The first instinct would be to use a date dimension and a time dimension and set page filters on those. But then you'd run into the situation where you say Date = 2016-01-08 OR Date = 2016-01-09. Separately you'd set a filter on time where Time >= 4:30p AND Time <= 3:00a. Then you'd get 12:00a-3:00a on both days, and 4:30p-11:59:59p on both days.

 

The second instinct might be to store a full date-time field in your fact table, and then you can just filter the single field. This would be a bad idea from compression and memory usage standpoint. Filters would also be relatively slow on this field due to its high cardinality (lots of unique values to check). A workaround here would be truncating seconds, so you're only storing datetime down to the minute or 5 minute granularity.

Doing this would be relatively okay from a performance and memory perspective, but it's inconvenient from a UI perspective, because end users will need to know start and end times (including where these are different due to OT or scheduling changes) and will need to set both start and end as filter criteria.

 

With a shift dimension, you can easily capture OT and schedule changes in ETL, and to look at one shift or multiple shifts, you can make single selections for each. Moreover, you can select non-contiguous shifts. You can have attributes of the shift, such as 'Night' vs 'Day', or shift length, to capture OT when it happens. You can then do things like select all night shifts, which would be impossible with a date-time filter in PBI. Or you can divide output by the number of hours in the shift.

 

**Edit** @itchyeyeballs beat me to it. Hopefully hearing it from two people makes it sound like an even better idea.

Hi,

 

I have quite same issue and thought you can help me out 🙂

 

I have a file in Power BI where I want to calculate the solve time only between 8:00 - 17:00, as per the given date and time in table. I followed your idea by doing page level filters but the problem is that It is counting whole duration (eg. if case is open on 04/02/2018 at 14:31 and closed on 05/02/2018 at 11:00 it is counting 20.48 hours and according to me it should count like, 2.29 hours for date 04/02/2018 and 3 hours for date 05/02/2018, so it should be 5.29 hours in total).

 

Is there some DAX query with Filter function might help? or there is some another page level filter can work on it?

 

In short time should be counted only between 8:00 to 17:00 for each day.

 

I hope you understood my question. 

 

Thanks in advance!

itchyeyeballs
Impactful Individual
Impactful Individual

First off (and you may already have this) It may be worth creating a shift lookup (dimension) table similar to a date table. This could include one record for every shift past and future and hold metadata such as start_date, end_date, start_time, end_time, shift_type (morning/evening), length, shift_number (unique integer starting at 1 to identify order of shifts), work_period(a group that sets of shifts belong to), supervisor etc.

 

Every shift in your data (fact) table would be referenced by a shift_id that links to the shift_number in the shift lookup (dimension) table. The shifts lookup would probably need to be manually created in Excel then imported with updates carried out as needed.

 

You could then just set up simple slicers to look at shift type or start time etc.

 

 As an extra option there is a custom visual (Advanced Timeline) that allows you to specify periods of time - https://app.powerbi.com/visuals/

but this will only work with start or end times, not both.

 

Creating a single slicer that dynamically includes all shifts that  have start and end times that intersect given bounds will be challenging, the logic of working out which shifts overlap a specified period is possible but you would have to work out how you were going to store the start and end poitns of your specified period. 

Thanks! This is great! 🙂 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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