Forum Discussion
Filter Start Time
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!
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.
2 Replies
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.