Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 Regards
Lucien
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 Regards
Lucien
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 |