Start Time Filter

[Anonymous]

Hello,

Need some help on the following.

So I have a table on my powerBI (see below, just an example).

Time Stamp	Hour	LPN
11/11/19 7:04 AM	7	LPN1
11/11/19 6:44 AM	6	LPN2
11/11/19 6:51 AM	6	LPN3
11/11/19 8:01 AM	8	LPN4
11/11/19 7:07 AM	7	LPN5
11/11/19 8:56 AM	8	LPN6
11/11/19 9:14 AM	9	LPN7
11/11/19 8:04 AM	8	LPN8
11/11/19 8:14 AM	8	LPN9
11/11/19 9:47 PM	21	LPN10
11/11/19 11:22 PM	23	LPN11
11/11/19 11:58 PM	23	LPN12
11/12/19 12:43 AM	0	LPN13
11/12/19 2:03 AM	2	LPN14
11/12/19 2:42 AM	2	LPN15
11/12/19 2:43 AM	2	LPN16
11/12/19 2:49 AM	2	LPN17
11/12/19 3:41 AM	3	LPN18
11/12/19 3:43 AM	3	LPN19
11/12/19 4:39 AM	4	LPN20
11/12/19 4:26 AM	4	LPN21
11/12/19 5:38 AM	5	LPN22
11/12/19 5:40 AM	5	LPN23
11/12/19 4:56 AM	4	LPN24

the challenge that I have is I want that if the user choose specific date on my Date Slicer.(I put a column with DateValue formula)

The data should start from 9PM from the previous day upto 8:59PM current date(which the user chosen)

Example would be is, if I or the user choose 12/18/19 on my slicer.

it will show Hour on my graph from 12/17 hour 21,22,23 and 12/18 hour 1 to 20.

hope it makes sense.

*so if you look at the graph below.(real data, not the table above)

it should show 12/17 9PM(hour 21) up to 12/18 8:59PM(hour20) (12/18 is the chosen filter on the slicer)

Appreciate any help. thanks!

[Thim]

Hey there.

Big help you already have the Hour seperated 🙂

I would make a formula like this

Work Date = IF('Table (2)'[Hour]>20;

DATE(YEAR('Table (2)'[Time Stamp]);MONTH('Table (2)'[Time Stamp]);DAY('Table (2)'[Time Stamp])+1);

DATE(YEAR('Table (2)'[Time Stamp]);MONTH('Table (2)'[Time Stamp]);DAY('Table (2)'[Time Stamp]))

)

Essentially you make a new Date column that adds a day if your Hour column has a number greater than 20.

Hope this will help. 🙂

[Anonymous]

Hello,

I got an error when I tried your formula. Did I forgot something?

Thanks

[Thim · Accepted solution]

I am guessing you are on a different version of Power BI than i am.

Try and change all the ; to ,

I beleive (don't quote me) that ; is the syntax on Dax formula that is used in EU, and , is used in US.

[Anonymous]

I got it, lol my bad. thank you very much.