cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
otl_86
Frequent Visitor

Calls Waiting To Be Returned

Please help##

 

I am looking to create a graph

Hours of day along the bottom

Columns – Calls waiting at that hour to be answered

 

How would I find out ‘Calls waiting to be answered?’

 

I have the date and time the call was created and the date and time an agent called back the person?

 

I’m guessing there should be a continuous count in there somewhere and then -1 of the count when a call has been returned?

 

This would not be a real time dash but looking back at past data

 

Thanks

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @otl_86 ,

Test the below ,first create a date table:

= Table.FromColumns({List.DateTimes(#datetime(2022,1,1,0,0,0),365*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

vluwangmsft_0-1652754217847.png

Then use the below measure:

sumaccount = IF([HOURcount1]<>BLANK(), CALCULATE(COUNTROWS('Table'),'Table'[START_DATEH]<=MAX('Table 1'[DateH])),BLANK())
sumaccount2 = if([sumfinal]<>BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[START_DATEH]<=MAX('Table 1'[DateH])),'Table 1'[sumaccount])
sumfinal = IF([finalcount1]<>BLANK(), CALCULATE(COUNTROWS('Table'),'Table'[condateh]<=MAX('Table 1'[DateH])),BLANK())
sumfinal2 = IF([sumaccount]<>BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[condateh]<=MAX('Table 1'[DateH])),[sumfinal])

 

Then create visual:

vluwangmsft_1-1652754304016.png

When I imported your data, the month and day were not accurately judged, so the results will be a little different, according to your actual situation to adjust.
The results will be adjusted according to your actual situation.

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @otl_86 ,

Test the below ,first create a date table:

= Table.FromColumns({List.DateTimes(#datetime(2022,1,1,0,0,0),365*1440,#duration(0,0,1,0))}, type table[DateTime=datetime])

vluwangmsft_0-1652754217847.png

Then use the below measure:

sumaccount = IF([HOURcount1]<>BLANK(), CALCULATE(COUNTROWS('Table'),'Table'[START_DATEH]<=MAX('Table 1'[DateH])),BLANK())
sumaccount2 = if([sumfinal]<>BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[START_DATEH]<=MAX('Table 1'[DateH])),'Table 1'[sumaccount])
sumfinal = IF([finalcount1]<>BLANK(), CALCULATE(COUNTROWS('Table'),'Table'[condateh]<=MAX('Table 1'[DateH])),BLANK())
sumfinal2 = IF([sumaccount]<>BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[condateh]<=MAX('Table 1'[DateH])),[sumfinal])

 

Then create visual:

vluwangmsft_1-1652754304016.png

When I imported your data, the month and day were not accurately judged, so the results will be a little different, according to your actual situation to adjust.
The results will be adjusted according to your actual situation.

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

PC2790
Super User
Super User

How does your data model look like?

Can you provide your sample data removing sensitive information.

Please follow - community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2467420#M880058

 

 

 

otl_86
Frequent Visitor

Case RefStartConsultation Advice StartConsultation Advice End
f7a0799e-cf3a-408b-93ef-ab850207fa3901/03/2022 18:5901/03/2022 20:0501/03/2022 20:15
fdc7ec09-5aae-4c39-a5ac-bb51bbd3b70701/03/2022 19:1901/03/2022 19:2501/03/2022 19:37
532cf5d7-346b-447a-8c74-7f83463b688e02/03/2022 01:0602/03/2022 01:4302/03/2022 02:12
34cd6084-56b2-4e05-ac5d-2017aaf5144802/03/2022 01:5702/03/2022 02:0002/03/2022 02:10
28cdeb00-8f5c-4aa7-850e-8f3c42f7747202/03/2022 04:1702/03/2022 04:1802/03/2022 04:28
bdf6363b-d952-4927-a8a9-484756f6730202/03/2022 20:4402/03/2022 20:5002/03/2022 20:58
5fc19d75-75a0-4b5a-b9e0-89ae3d87a1d602/03/2022 22:3502/03/2022 23:1402/03/2022 23:20
fa741737-c108-414f-b8f5-e138e10f79b303/03/2022 00:0703/03/2022 01:3003/03/2022 01:36
636ecb9f-ae7a-4c50-9d94-474be02f9f5d03/03/2022 00:1003/03/2022 00:4903/03/2022 00:50
529e01bc-8aec-400b-b917-a1f16c0db9cc03/03/2022 00:1203/03/2022 00:5103/03/2022 00:53
0b985dbc-b9f7-4c55-ae7b-99841b76ea2f03/03/2022 00:1303/03/2022 00:3803/03/2022 00:49
27982d7d-6350-475a-a15f-de2dfc56398003/03/2022 01:2003/03/2022 01:3203/03/2022 01:40
8f7b4900-2871-4eeb-a1f6-1c1c22bfa4d203/03/2022 19:0203/03/2022 19:0403/03/2022 19:24
630bc08e-ccfa-4350-9c5a-c5155441c4b703/03/2022 19:5203/03/2022 20:0803/03/2022 20:34
b0b10b15-61a4-4273-a58c-a9a68e66093803/03/2022 21:4803/03/2022 21:5403/03/2022 22:25
76357075-2699-426c-8823-7879564f225f03/03/2022 22:1203/03/2022 22:5103/03/2022 22:57
f038d826-bef2-494a-b460-5f209ec658bc03/03/2022 22:5804/03/2022 00:3604/03/2022 00:40
e0269fc5-6b14-4f03-a4ec-99428131fd9b04/03/2022 01:2904/03/2022 01:3304/03/2022 02:15
74935679-d938-41ec-8ba1-d3ec1391fdf704/03/2022 01:5804/03/2022 02:1504/03/2022 02:25
e7345d37-9a05-4544-9d55-92c36dee975f04/03/2022 02:0704/03/2022 02:3304/03/2022 02:42
291e3d68-2b9e-452d-9c73-45e318d6b50004/03/2022 03:3404/03/2022 03:4804/03/2022 03:53
7c6e447d-8a4b-4435-9203-545de8f22fab04/03/2022 20:3404/03/2022 21:1004/03/2022 21:34
daf97562-d33d-495e-8fb1-ec257a94748e04/03/2022 20:4804/03/2022 21:0204/03/2022 21:15
599d2143-4b6f-4897-a385-3fbc1952408b04/03/2022 21:5404/03/2022 22:2504/03/2022 22:43
9a539599-16d9-4531-badc-5e4ac1ef2db704/03/2022 22:2504/03/2022 22:3704/03/2022 23:02
4ab9b2f6-4acb-4309-a0f0-66203395918904/03/2022 23:1904/03/2022 23:2704/03/2022 23:45
2c1f4b4e-7ca0-4831-a226-adc0404b0ede05/03/2022 00:4505/03/2022 03:2405/03/2022 07:44
f40da88d-e063-427f-a967-432d7925378605/03/2022 07:0805/03/2022 07:1405/03/2022 07:25
b9743c1b-b48f-465a-8185-cfc75bd6e82b05/03/2022 08:4805/03/2022 09:0805/03/2022 09:45
3aa8dede-1dc4-46e1-a271-9b96f93c279e05/03/2022 09:0405/03/2022 09:0605/03/2022 09:10
6794d78d-e3bc-4d97-8be6-051ca5403d4d05/03/2022 09:5105/03/2022 10:2105/03/2022 10:29
323e9c06-09d1-495e-900a-1a5a5bd3b1c905/03/2022 09:5105/03/2022 09:5605/03/2022 10:16
607c85d8-9307-4606-8484-2a240a2dfb3d05/03/2022 10:2305/03/2022 10:5105/03/2022 11:01
183bdad7-3587-4f38-b9e5-fe3adb9308dd05/03/2022 10:2705/03/2022 10:3205/03/2022 10:56
08a62978-db70-45e3-8f65-4a18a14dd9bd05/03/2022 10:2505/03/2022 12:3805/03/2022 13:20
14d2337b-32b8-489b-a7b3-702bb579cea405/03/2022 10:3405/03/2022 10:4305/03/2022 10:56
8d02acab-abc8-4f26-85c8-082e6f8cb54405/03/2022 10:5705/03/2022 11:4005/03/2022 11:48
6be23dad-96e9-48fb-919c-88325d562c3405/03/2022 11:1005/03/2022 11:1105/03/2022 11:16
3f99044e-98b5-47e7-8392-4bc835fb234e05/03/2022 11:4005/03/2022 16:2305/03/2022 16:28
1df523ac-3236-44b5-9a78-1cb80a4033a405/03/2022 13:1805/03/2022 13:2405/03/2022 14:47
7585edd0-5c78-4249-892f-99925ce0d85e05/03/2022 17:1605/03/2022 17:2505/03/2022 17:29
7c87919a-8440-4c6d-a870-8d88ecd2a66e05/03/2022 17:2905/03/2022 17:3705/03/2022 17:45
77edeac3-6649-427e-85c6-af19d020ac7b05/03/2022 19:0405/03/2022 19:3605/03/2022 20:08
7cf546bc-d347-4311-80bf-32966cc78ab505/03/2022 19:2505/03/2022 20:0505/03/2022 20:41
ddff5ed1-3371-4e5a-a664-27a955279e5e05/03/2022 20:0105/03/2022 20:2805/03/2022 21:05
41794b4c-ceda-4ed0-86cd-45064ea0830905/03/2022 21:3805/03/2022 23:1206/03/2022 00:09
1a3480bf-f761-4ad1-8bdb-91334cb741df05/03/2022 22:3705/03/2022 23:0405/03/2022 23:50
otl_86
Frequent Visitor

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors