Forum Discussion
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
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])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:
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 RegardsLucien
4 Replies
PC2790Community Champion
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
Case Ref Start Consultation Advice Start Consultation Advice End f7a0799e-cf3a-408b-93ef-ab850207fa39 01/03/2022 18:59 01/03/2022 20:05 01/03/2022 20:15 fdc7ec09-5aae-4c39-a5ac-bb51bbd3b707 01/03/2022 19:19 01/03/2022 19:25 01/03/2022 19:37 532cf5d7-346b-447a-8c74-7f83463b688e 02/03/2022 01:06 02/03/2022 01:43 02/03/2022 02:12 34cd6084-56b2-4e05-ac5d-2017aaf51448 02/03/2022 01:57 02/03/2022 02:00 02/03/2022 02:10 28cdeb00-8f5c-4aa7-850e-8f3c42f77472 02/03/2022 04:17 02/03/2022 04:18 02/03/2022 04:28 bdf6363b-d952-4927-a8a9-484756f67302 02/03/2022 20:44 02/03/2022 20:50 02/03/2022 20:58 5fc19d75-75a0-4b5a-b9e0-89ae3d87a1d6 02/03/2022 22:35 02/03/2022 23:14 02/03/2022 23:20 fa741737-c108-414f-b8f5-e138e10f79b3 03/03/2022 00:07 03/03/2022 01:30 03/03/2022 01:36 636ecb9f-ae7a-4c50-9d94-474be02f9f5d 03/03/2022 00:10 03/03/2022 00:49 03/03/2022 00:50 529e01bc-8aec-400b-b917-a1f16c0db9cc 03/03/2022 00:12 03/03/2022 00:51 03/03/2022 00:53 0b985dbc-b9f7-4c55-ae7b-99841b76ea2f 03/03/2022 00:13 03/03/2022 00:38 03/03/2022 00:49 27982d7d-6350-475a-a15f-de2dfc563980 03/03/2022 01:20 03/03/2022 01:32 03/03/2022 01:40 8f7b4900-2871-4eeb-a1f6-1c1c22bfa4d2 03/03/2022 19:02 03/03/2022 19:04 03/03/2022 19:24 630bc08e-ccfa-4350-9c5a-c5155441c4b7 03/03/2022 19:52 03/03/2022 20:08 03/03/2022 20:34 b0b10b15-61a4-4273-a58c-a9a68e660938 03/03/2022 21:48 03/03/2022 21:54 03/03/2022 22:25 76357075-2699-426c-8823-7879564f225f 03/03/2022 22:12 03/03/2022 22:51 03/03/2022 22:57 f038d826-bef2-494a-b460-5f209ec658bc 03/03/2022 22:58 04/03/2022 00:36 04/03/2022 00:40 e0269fc5-6b14-4f03-a4ec-99428131fd9b 04/03/2022 01:29 04/03/2022 01:33 04/03/2022 02:15 74935679-d938-41ec-8ba1-d3ec1391fdf7 04/03/2022 01:58 04/03/2022 02:15 04/03/2022 02:25 e7345d37-9a05-4544-9d55-92c36dee975f 04/03/2022 02:07 04/03/2022 02:33 04/03/2022 02:42 291e3d68-2b9e-452d-9c73-45e318d6b500 04/03/2022 03:34 04/03/2022 03:48 04/03/2022 03:53 7c6e447d-8a4b-4435-9203-545de8f22fab 04/03/2022 20:34 04/03/2022 21:10 04/03/2022 21:34 daf97562-d33d-495e-8fb1-ec257a94748e 04/03/2022 20:48 04/03/2022 21:02 04/03/2022 21:15 599d2143-4b6f-4897-a385-3fbc1952408b 04/03/2022 21:54 04/03/2022 22:25 04/03/2022 22:43 9a539599-16d9-4531-badc-5e4ac1ef2db7 04/03/2022 22:25 04/03/2022 22:37 04/03/2022 23:02 4ab9b2f6-4acb-4309-a0f0-662033959189 04/03/2022 23:19 04/03/2022 23:27 04/03/2022 23:45 2c1f4b4e-7ca0-4831-a226-adc0404b0ede 05/03/2022 00:45 05/03/2022 03:24 05/03/2022 07:44 f40da88d-e063-427f-a967-432d79253786 05/03/2022 07:08 05/03/2022 07:14 05/03/2022 07:25 b9743c1b-b48f-465a-8185-cfc75bd6e82b 05/03/2022 08:48 05/03/2022 09:08 05/03/2022 09:45 3aa8dede-1dc4-46e1-a271-9b96f93c279e 05/03/2022 09:04 05/03/2022 09:06 05/03/2022 09:10 6794d78d-e3bc-4d97-8be6-051ca5403d4d 05/03/2022 09:51 05/03/2022 10:21 05/03/2022 10:29 323e9c06-09d1-495e-900a-1a5a5bd3b1c9 05/03/2022 09:51 05/03/2022 09:56 05/03/2022 10:16 607c85d8-9307-4606-8484-2a240a2dfb3d 05/03/2022 10:23 05/03/2022 10:51 05/03/2022 11:01 183bdad7-3587-4f38-b9e5-fe3adb9308dd 05/03/2022 10:27 05/03/2022 10:32 05/03/2022 10:56 08a62978-db70-45e3-8f65-4a18a14dd9bd 05/03/2022 10:25 05/03/2022 12:38 05/03/2022 13:20 14d2337b-32b8-489b-a7b3-702bb579cea4 05/03/2022 10:34 05/03/2022 10:43 05/03/2022 10:56 8d02acab-abc8-4f26-85c8-082e6f8cb544 05/03/2022 10:57 05/03/2022 11:40 05/03/2022 11:48 6be23dad-96e9-48fb-919c-88325d562c34 05/03/2022 11:10 05/03/2022 11:11 05/03/2022 11:16 3f99044e-98b5-47e7-8392-4bc835fb234e 05/03/2022 11:40 05/03/2022 16:23 05/03/2022 16:28 1df523ac-3236-44b5-9a78-1cb80a4033a4 05/03/2022 13:18 05/03/2022 13:24 05/03/2022 14:47 7585edd0-5c78-4249-892f-99925ce0d85e 05/03/2022 17:16 05/03/2022 17:25 05/03/2022 17:29 7c87919a-8440-4c6d-a870-8d88ecd2a66e 05/03/2022 17:29 05/03/2022 17:37 05/03/2022 17:45 77edeac3-6649-427e-85c6-af19d020ac7b 05/03/2022 19:04 05/03/2022 19:36 05/03/2022 20:08 7cf546bc-d347-4311-80bf-32966cc78ab5 05/03/2022 19:25 05/03/2022 20:05 05/03/2022 20:41 ddff5ed1-3371-4e5a-a664-27a955279e5e 05/03/2022 20:01 05/03/2022 20:28 05/03/2022 21:05 41794b4c-ceda-4ed0-86cd-45064ea08309 05/03/2022 21:38 05/03/2022 23:12 06/03/2022 00:09 1a3480bf-f761-4ad1-8bdb-91334cb741df 05/03/2022 22:37 05/03/2022 23:04 05/03/2022 23:50
- v-luwang-msft
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])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:
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 RegardsLucien
