cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gauravnarchal
Post Prodigy
Post Prodigy

How to calculate the number of calls per hour?

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..

 

TimeDateCalling Party Number
2:06:39 AM GSTDec 12, 2022757575
3:54:42 AM GSTDec 12, 202242342342
8:05:41 AM GSTDec 12, 2022342424
8:17:15 AM GSTDec 12, 202234234234
8:24:25 AM GSTDec 12, 202234234
8:37:23 AM GSTDec 12, 2022234
8:42:07 AM GSTDec 12, 202223423
8:45:30 AM GSTDec 12, 20224214324
8:45:30 AM GSTDec 12, 2022234234
8:49:55 AM GSTDec 12, 20222342
8:50:23 AM GSTDec 12, 202234234234
8:56:43 AM GSTDec 12, 20223423423
8:58:23 AM GSTDec 12, 202223423423
9:00:37 AM GSTDec 12, 202224234
9:01:54 AM GSTDec 12, 20222562562
9:07:49 AM GSTDec 12, 20226262626
9:11:25 AM GSTDec 12, 20222462625
9:12:46 AM GSTDec 12, 202267367365
9:13:06 AM GSTDec 12, 2022878478
9:14:02 AM GSTDec 12, 20228946947
9:14:40 AM GSTDec 12, 2022336736789
9:17:30 AM GSTDec 12, 202247984794
9:19:54 AM GSTDec 12, 2022537373568
9:21:59 AM GSTDec 12, 20224783837
9:22:53 AM GSTDec 12, 202236736738
9:24:55 AM GSTDec 12, 202236836738
9:28:23 AM GSTDec 12, 20223683838
9:29:44 AM GSTDec 12, 202236838
9:38:09 AM GSTDec 12, 20223883
9:39:34 AM GSTDec 12, 202245745747
9:40:05 AM GSTDec 12, 20223868368
9:43:28 AM GSTDec 12, 20223683658
9:45:04 AM GSTDec 12, 2022358368
9:45:31 AM GSTDec 12, 20225345346
9:50:04 AM GSTDec 12, 2022346346
9:55:30 AM GSTDec 12, 20223466346
9:56:38 AM GSTDec 12, 20228838
10:01:21 AM GSTDec 12, 202263467
10:03:53 AM GSTDec 12, 20226655655
10:03:57 AM GSTDec 12, 2022457457
10:05:05 AM GSTDec 12, 20227457457

 

 

1 ACCEPTED SOLUTION
Bifinity_75
Solution Sage
Solution Sage

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):

Bifinity_75_0-1670867350278.png

With this calculated column it is easy to create the measure to count the number of calls.

Best regards

 

View solution in original post

1 REPLY 1
Bifinity_75
Solution Sage
Solution Sage

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):

Bifinity_75_0-1670867350278.png

With this calculated column it is easy to create the measure to count the number of calls.

Best regards

 

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors