Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, How can I calculate the number of calls received per hour? Can I create a column to get this result?
For example, I would like to know how many calls were received between
12am to 1am
1am to 2am
2am to 3am
3am to 4am
4am to 5am
5am to 6am and so on……….. Thanks
Below is how my data looks like..
Time | Date | Calling Party Number |
2:06:39 AM GST | Dec 12, 2022 | 757575 |
3:54:42 AM GST | Dec 12, 2022 | 42342342 |
8:05:41 AM GST | Dec 12, 2022 | 342424 |
8:17:15 AM GST | Dec 12, 2022 | 34234234 |
8:24:25 AM GST | Dec 12, 2022 | 34234 |
8:37:23 AM GST | Dec 12, 2022 | 234 |
8:42:07 AM GST | Dec 12, 2022 | 23423 |
8:45:30 AM GST | Dec 12, 2022 | 4214324 |
8:45:30 AM GST | Dec 12, 2022 | 234234 |
8:49:55 AM GST | Dec 12, 2022 | 2342 |
8:50:23 AM GST | Dec 12, 2022 | 34234234 |
8:56:43 AM GST | Dec 12, 2022 | 3423423 |
8:58:23 AM GST | Dec 12, 2022 | 23423423 |
9:00:37 AM GST | Dec 12, 2022 | 24234 |
9:01:54 AM GST | Dec 12, 2022 | 2562562 |
9:07:49 AM GST | Dec 12, 2022 | 6262626 |
9:11:25 AM GST | Dec 12, 2022 | 2462625 |
9:12:46 AM GST | Dec 12, 2022 | 67367365 |
9:13:06 AM GST | Dec 12, 2022 | 878478 |
9:14:02 AM GST | Dec 12, 2022 | 8946947 |
9:14:40 AM GST | Dec 12, 2022 | 336736789 |
9:17:30 AM GST | Dec 12, 2022 | 47984794 |
9:19:54 AM GST | Dec 12, 2022 | 537373568 |
9:21:59 AM GST | Dec 12, 2022 | 4783837 |
9:22:53 AM GST | Dec 12, 2022 | 36736738 |
9:24:55 AM GST | Dec 12, 2022 | 36836738 |
9:28:23 AM GST | Dec 12, 2022 | 3683838 |
9:29:44 AM GST | Dec 12, 2022 | 36838 |
9:38:09 AM GST | Dec 12, 2022 | 3883 |
9:39:34 AM GST | Dec 12, 2022 | 45745747 |
9:40:05 AM GST | Dec 12, 2022 | 3868368 |
9:43:28 AM GST | Dec 12, 2022 | 3683658 |
9:45:04 AM GST | Dec 12, 2022 | 358368 |
9:45:31 AM GST | Dec 12, 2022 | 5345346 |
9:50:04 AM GST | Dec 12, 2022 | 346346 |
9:55:30 AM GST | Dec 12, 2022 | 3466346 |
9:56:38 AM GST | Dec 12, 2022 | 8838 |
10:01:21 AM GST | Dec 12, 2022 | 63467 |
10:03:53 AM GST | Dec 12, 2022 | 6655655 |
10:03:57 AM GST | Dec 12, 2022 | 457457 |
10:05:05 AM GST | Dec 12, 2022 | 7457457 |
Solved! Go to Solution.
Hi @gauravnarchal , you can create a calculate column:
Time_ = SWITCH(
TRUE(),
'Table'[Time]>=TIME(12,0,0) && 'Table'[Time]<TIME(1,0,0),"12am to 1 am",
'Table'[Time]>=TIME(1,0,0) && 'Table'[Time]<TIME(2,0,0),"1 am to 2 am",
'Table'[Time]>=TIME(2,0,0) && 'Table'[Time]<TIME(3,0,0),"2 am to 3 am",
'Table'[Time]>=TIME(3,0,0) && 'Table'[Time]<TIME(4,0,0),"3 am to 4 am",
'Table'[Time]>=TIME(4,0,0) && 'Table'[Time]<TIME(5,0,0),"4 am to 5 am",
'Table'[Time]>=TIME(5,0,0) && 'Table'[Time]<TIME(6,0,0),"5 am to 6 am",
'Table'[Time]>=TIME(6,0,0) && 'Table'[Time]<TIME(7,0,0),"6 am to 7 am",
'Table'[Time]>=TIME(7,0,0) && 'Table'[Time]<TIME(8,0,0),"7 am to 8 am",
...........
...........
...........
""
)
Your "Time Column" must have time format (Hour):
With this calculated column it is easy to create the measure to count the number of calls.
Best regards
Hi @gauravnarchal , you can create a calculate column:
Time_ = SWITCH(
TRUE(),
'Table'[Time]>=TIME(12,0,0) && 'Table'[Time]<TIME(1,0,0),"12am to 1 am",
'Table'[Time]>=TIME(1,0,0) && 'Table'[Time]<TIME(2,0,0),"1 am to 2 am",
'Table'[Time]>=TIME(2,0,0) && 'Table'[Time]<TIME(3,0,0),"2 am to 3 am",
'Table'[Time]>=TIME(3,0,0) && 'Table'[Time]<TIME(4,0,0),"3 am to 4 am",
'Table'[Time]>=TIME(4,0,0) && 'Table'[Time]<TIME(5,0,0),"4 am to 5 am",
'Table'[Time]>=TIME(5,0,0) && 'Table'[Time]<TIME(6,0,0),"5 am to 6 am",
'Table'[Time]>=TIME(6,0,0) && 'Table'[Time]<TIME(7,0,0),"6 am to 7 am",
'Table'[Time]>=TIME(7,0,0) && 'Table'[Time]<TIME(8,0,0),"7 am to 8 am",
...........
...........
...........
""
)
Your "Time Column" must have time format (Hour):
With this calculated column it is easy to create the measure to count the number of calls.
Best regards
Did this sort out your issue?
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |