Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Im trying to create the following in PowerBI. All open calls (calls that dont have a completion date), in a stacked column chart, where a selection is made on how long a call is already open, with status of the call (status: in progress, on hold, awaiting, in transfer). So this should be counted from the calldate (startdate) of the call, and with the use of the current date at that moment to determine how long a call is open.
How many days a call is open, should determine in what "group" it will be in above visual. For example see the following table:
The following tables I use:
Calls ; contains all incoming calls
Both calldate and completiondate have a date hierarchy with year, month, and days
Status ; which contains following statuses:
I use a filter to exclude the "logged" and "closed" status to only get the open calls
How can I group the calls together based on the time they are open??
Solved! Go to Solution.
Hi, @TechR21
Based on the data you provide.
Column:
days a call is open = [Current date]-[Call date]+1
Group = SWITCH(TRUE(),
[days a call is open]<=2,"2days",
[days a call is open]>2&&[days a call is open]<=5,"2-5days",
[days a call is open]>5&&[days a call is open]<=7,"5-7days",
[days a call is open]>7&&[days a call is open]<=14,"1-2weeks",
[days a call is open]>14&&[days a call is open]<=28,"2-4weeks",
[days a call is open]>28&&[days a call is open]<=61,"1-2months",
[days a call is open]>61,">2months")
If this is not the result you expect, please provide more information.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @TechR21
Based on the data you provide.
Column:
days a call is open = [Current date]-[Call date]+1
Group = SWITCH(TRUE(),
[days a call is open]<=2,"2days",
[days a call is open]>2&&[days a call is open]<=5,"2-5days",
[days a call is open]>5&&[days a call is open]<=7,"5-7days",
[days a call is open]>7&&[days a call is open]<=14,"1-2weeks",
[days a call is open]>14&&[days a call is open]<=28,"2-4weeks",
[days a call is open]>28&&[days a call is open]<=61,"1-2months",
[days a call is open]>61,">2months")
If this is not the result you expect, please provide more information.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
perfect! i figured it out, thank you
@TechR21 , Not clear on the ask.
You can get Days in a new column
datediff([Call date], today(), day)
Then you can use switch to create the group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |