Range slicer by time of day AND NOT by date.

[Element115]

I have the following business requirement:

Problem statement: Let the user slice the data using not only by a date range slicer, but also a time range slicer.

Meaning:  User can select start date 12/01/2020 to end date 12/07/2020 (using US date format) AND start time 14:45 to end time 20:15, for example.

Discussion:  The date range is no problem as Power BI already provides a built-in slicer capable of working with dates.  The problem is the time slicer, ie a slicer capable of working with hours and minutes to define an intraday range, just like a regular slicer is capable of defining a date range.  The built-in slicer doesn't do that.

Question:  Is there any way of achieving this? If yes, how?  DAX formulas? A custom visual from a 3rd party (have looked but didn't find anything)? 

[Element115 · Accepted solution]

I am happy to annouce that I finally managed to solve this and must say: The Power BI Tabular platform makes the implementation of this kind of solution extremely tricky! You've got to consider 4 different levels of filters. How insane is that for an algo so simple it would have taken me 5 minutes or less to implement in C or Python. 

[Farhad88]

Dear @Element115 ,

I have the same requirement, and so far, I couldn't implement it using Power BI.

Could you please share your solution or the pbix file?

Thanks and best regard,

Farhad

[Syndicate_Admin]

Where is the solution?

[Element115]

What do you mean?  Was I supposed to post the solution?

[Fowmy]

@Element115 

I came up with a solution, please check the attached file. I duplicated the date table to provide an additional slicer for time. You will have to select the date range then Time Start and Time End.

You can download the file: HERE

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Farhad88]

Hi @Fowmy ,

Would you please upload the pbix file again? (I couldn't dowload it using the old link)

Thanks and best regards,

Farhad

[SDisley]

Any chance you can reshare the file

[Element115]

Thank you @Fowmy I am afraid I was not clear enough in my problem def--I realize now I implied what I am going to write, and I apologize for that. Your proposed solution unfortunately only creates a time range--the same time range--within each day.  

The requirement I have is to define a date-time range across multiple days. In other words, for ex.:

start date-time: 12/01/2020 13:15:00

end date-time: 12/04/2020 15:30:00

so that all time points between these 2 dates are included in a table, and not the time points from 13:15:00 to 15:30:00 only for each date, ie

NOT this (let's assume an interval of 15 mins for each time point):

12/01/2020 13:15:00

12/01/2020 13:30:00

12/01/2020 13:45:00

12/01/2020 14:00:00

12/01/2020 14:15:00

12/01/2020 14:30:00

12/01/2020 14:45:00

12/01/2020 15:00:00

12/01/2020 15:15:00

12/01/2020 15:30:00

12/02/2020 13:15:00

12/02/2020 13:30:00

12/02/2020 13:45:00

12/02/2020 14:00:00

12/02/2020 14:15:00

12/02/2020 14:30:00

12/02/2020 14:45:00

12/02/2020 15:00:00

12/02/2020 15:15:00

12/02/2020 15:30:00

...
all the way like this til 12/04/2020.

Instead, we should also get the time points after 15:30:00 from start date until end date for all dates in between as a continuous timeline (by 'until end date' I mean the last time point on the end date would be 15:30:00 in this example, but the day before we would have time points all the way to midnight and continuing through the morning of the last date in the range to end at 15:30:00), and not only the same start time-end time range repeated for each day in the date range.

Is this explanation clearer?