Slice by Hour

[davehardikkumar]

Hello,

I have live conneciton and I want to fiter the data by hour range like I want to filter after 4.30 pm. I have date and time in one column.

How can I create 24 hour filter which would slice the data? I have tried to use relative time but it give me whole hour.

Please guide me in the direction.

Thank you!

[tamerj1]

Hi @ValtteriN 
Please refer to attached sample file with the proposed solution.

[davehardikkumar]

Thank you for your help

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

Data:

Dax:

Filter after 4.30 =

var _selectedvalue = MAX('Table (30)'[Column1])

var _hour = HOUR(_selectedvalue)

var _min = MINUTE(_selectedvalue)

return

SWITCH(TRUE(),

_hour>=17,1, //check for after 17  

_hour>=16 && _min >= 30, 1, // check for 16:30-17

0) //if not these then 0

End result and explanation:

Place the measure as a filter like in the picture. Afterthis you can apply relative date filter of 1 day. Since the measure will only allow values after 16.30 (4.30 pm) the result should be like you described.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[davehardikkumar]

Thanks for your help but I want user to pick any hournot exac 4.30 and then filter the data. so how can I achieve that?

[ValtteriN]

In that case you can use parameters. e.g.

Filter after 4.30 dynamic =

var _selectedvalue = MAX('Table (30)'[Column1])

var _hour = HOUR(_selectedvalue)

var _min = MINUTE(_selectedvalue)

return

SWITCH(TRUE(),

_hour>=[Hour Value] && _min >= [Minute Value], 1,

0)

Result:
So insert parameters from here:
For hours 0 to 23 and for minutes 0 to 59. Increment 1

[P_d2023]

Hi, you can split the hour from the date/column in the data view.

if you create a custom column and use the below dax expression. you can then use the hour in a seperate filter.

this will round the hour back so 20:59 would show as 20

[davehardikkumar]

as I am using live connection, I cant create column or table. do you have any other way?