Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Woah, thank you very much this is the one I'm looking for. thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |