Overview calls based on status and time period

[TechR21]

Hi ,

Im trying to get the following in PowerBI, without results so far:

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 to determine how long a call is open.

Anyone an idea how to create this? the main problem is to "bundle" the calls together so that they are grouped in "same day", "2 days" etc

The following tables I use:

Calls ; contains all incoming calls

• Callid (id of the call, number of calls)
• Calldate (startdate )
• Completiondate (when a call is closed)

Both calldate and completiondate have a date hierarchy with year, month, and days

Status ; which contains following statuses:

• Logged
  
• awaiting
• In progress
• On hold
• In transfer
• Closed
  

I use a filter to exclude the "logged" and "closed" status to only get the open calls

[v-yueyunzh-msft]

Hi , @TechR21 

For your question, I'm sorry that i  don't know your business logic very well, here are my questions:
(1) Status is inconsistent with what you show in the figure, where is "awaiting" judged
(2) How do you judge the call "Same Dat, 2Days,2-5Days..."
(3)"The main problem is to "bundle" the calls together so that they are grouped in "same day", what exactly does "2 days" etc mean, I don't know much about your table structure, can you provide some sample data and the desired output data? (Form or .pbix file)

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TechR21]

Hi,

I created a new question, which is more clear hopefully.

https://community.powerbi.com/t5/Desktop/Grouping-of-calls-to-use-in-a-report/m-p/2894783#M995044

[v-yueyunzh-msft]

Hi , @TechR21 

I see your new case , and i read your need , do you want to get the "Same Dat, 2Days,2-5Days..." by the column [Calldate]?

If this , you can refer to my test data:

(1)We can click "New column" to create a column to calcualte the days we need :

Days a call is open = IF( 'Table'[Status] in {"Logged","Closed"} , BLANK() , ABS(DATEDIFF('Table'[Calldate],TODAY(),DAY)+1))

(2)We need to click "New Column" to calculate another column like this:

Column = SWITCH( TRUE() , [Days a call is open] =1,"Same Day", [Days a call is open]=2 ,"2 days", [Days a call is open] >2 && [Days a call is open]<=5,"2-5 days",[Days a call is open] >5 && [Days a call is open]<=7 ,"5-7 days",
   [Days a call is open] >7 && [Days a call is open]<=14 ,"1-2 weeks",[Days a call is open] >14 && [Days a call is open]<=28,"2-4 weeks",[Days a call is open] >28 && [Days a call is open]<=60,"1-2 months",[Days a call is open]>60,">2month")

(3)Then we can get the data you need like this:

If this is not you want , you can use the sample data to me and the output data like a table you want so that we can help you better .

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TechR21]

Hi,

the table provided with the data in, is not an actual table at the moment: Its to give an example of how the calculation is done. "days a call is open" and "group" do not excist at the moment

As stated i use a call table and status table. This data I have to use.

In the call table i have columns like calldate, completiondate, callid etc. This table contains more then 1000 rows

In the status table i have columns like statusid, status(name)

so basically I need

A) a calculation of how long a call is open  and

B) using this calculation to group the data together

Important to know is that call table gets updated when data changes obviously. Also there are more tables in the whole datamodel, so I dont know if creating a new table is that convenient

[v-yueyunzh-msft]

Hi , @TechR21 

According to your description, you have two table "call" and "status". Right?

I think it has relationship between two tables so that you can judge what is this call's status. If you want to get the visual you want in your first description. I think the best way is to LOOKUPVALUE the call's status to your "Call" table  . Then you need to create calculated column in your "Call" table to calculate the "days call is open" and "group" you need .

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly