Filter Start Time

[Anonymous]

Hello,

Need some help with my new project. I'm still learning on how to code, and i got stucked on the following.

Below is my table.

Time Stamp	Hour	# of Cases
11/11/19 7:04 AM	7	1
11/11/19 6:44 AM	6	1
11/11/19 6:51 AM	6	1
11/11/19 8:01 AM	8	1
11/11/19 7:07 AM	7	1
11/11/19 8:56 AM	8	1
11/11/19 9:14 AM	9	1
11/11/19 8:04 AM	8	1
11/11/19 8:14 AM	8	1
11/11/19 9:47 PM	21	1
11/11/19 11:22 PM	23	1
11/11/19 11:58 PM	23	1
11/12/19 12:43 AM	0	1
11/12/19 2:03 AM	2	1
11/12/19 2:42 AM	2	1
11/12/19 2:43 AM	2	1
11/12/19 2:49 AM	2	1
11/12/19 3:41 AM	3	1
11/12/19 3:43 AM	3	1
11/12/19 4:39 AM	4	1
11/12/19 4:26 AM	4	1
11/12/19 5:38 AM	5	1
11/12/19 5:40 AM	5	1
11/12/19 4:56 AM	4	1

Basically im making a graph that show how many cases each hour.

But i want to put a Filter that drop down a Date. (I want the user to just pick the date they want to see)

But the twist is. Example if the drop down i choose is 11/12/19, i want it to show the hours from 11/11/19 Hour 21 up to hour 20 of 11/12.

Basically hour start time is from 10PM up to 9:59PM(next day). hope it makes sense. Thank you!

[Anonymous · Accepted solution]

Hi @pamboys09 ,

Please check following steps as below:

1. Create calculated table as slicer:

Table 2 = CALENDARAUTO()

2. Create measure:

Measure =

VAR td =

    FORMAT ( SELECTEDVALUE ( 'Table 2'[Date] ), "YYYYMMDD" ) & 20

VAR pd =

    FORMAT ( PREVIOUSDAY ( 'Table 2'[Date] ), "YYYYMMDD" ) & 21

RETURN

    CALCULATE (

        SUM ( 'Table'[# of Cases] ),

        FILTER (

            'Table',

            FORMAT ( 'Table'[Time Stamp], "YYYYMMDDHH" ) >= pd

                && FORMAT ( 'Table'[Time Stamp], "YYYYMMDDHH" ) <= td

        )

    )

3. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Woah, thank you very much this is the one I'm looking for. thanks again!