cancel
Showing results for
Did you mean:
Helper I

## Calculate number of calls open based on 2 date fields, call open date and call close date

Hi, I've been struggling awhile with this, reading posts about creating a date table then using calculate and filter but I can't seem to make it work for my requirement... I want to countrows PER DAY where:

CallsCurrentlyOpen = CallSubmitDate < \$DateEnteredByUserInAFilter\$ AND (CallResolvedDate >= \$DateEnteredByUserInAFilter\$ OR CallResolvedDate = "" (i.e. null), AND I want to apply slicers or filters of other fields on the result. E.g. I might filter based on which team the call was resolved by, which city the call was resolved in, and many other factors (About 25 other fields in the table. ) Can this be done?

In my data I have CallSubmitDate, CallResolvedDate, CallID, AssignedTeam. If the user adjusts a date filter, I would like to see how many calls are still open on any one day beteween that range, and have the option to select which team it occured on.

E.g.

Data looks like this:

CallSubmitDate; CallResolvedDate; AssignedTeam; CallID

jan 1, 2017; Jan 5, 2017; Team1; 001

Jan 3, 2017; Jan 7, 2017; Team2; 002

Jan 6, 2017; null; Team2; 003

(I have about 1million rows of data here)

End Report User does this:

Filters on date range Jan 6, 2017 - Jan 9, 2017

Filters on AssignedTeam = Team2

Bar chart shows

jan 4, jan 5, jan 6, jan 7, jan 8, jan 9

each bar per day shows how many calls were open at the end of that day on Team2, so...

Jan4 = 1 (ID=002)

Jan5 = 1 (ID=002)

Jan6 = 1 (ID=002 - Note: at midnight of Jan 6, 003 was not open yet)

Jan7 = 2 (ID=002 & 003 - Note: at midnight of Jan 7, 002 was still open)

Jan8 = 1

Jan9 = 1

Best,

Ben

1 ACCEPTED SOLUTION
Super User III

Hi @bengisby,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User III

Hi @bengisby,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Ashish, thanks very much for taking the time. Apologies if I'm misunderstanding something, but my data does not have the ID repeated multiple times for dates it was open. The key is the ID, it is a unique value on the table, so I don't get a "3" repeating like that in my data, do I have to build a table with a function or something to make my data look like your 'Data' table?

Best,

Ben

Super User III

You are welcome.  Please download my workbook and study my solution.  I have remodelled your data in the backend to get the desired result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Yes, I see now, I walked through the steps in the query builder. Very nice solution. Ashish, do you think there will be a problem that the data could become too large expanding my table this way? I already have 1million rows of data, and if the average open time of a ticket is, say, 30 days (anywhere from 1 - 900 days currently) then the # of rows would be 30million. Is this OK?

Super User III

Hi,

Thank you.  That would be established only when you load all your data.  Please report back and let me know of the file's performance.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hello, thanks again, it took me all morning and I learned a lot, and it works. Performance is really not too bad, only a couple of minutes.

Super User III

Good to hear that.  Happy to help.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi Ashish,

It looks as though you provided a neat answer to the above - I think you stored it on OneDrive and unfortunately I'm not able to access from work.

Would you be kind enough to add some explanation to the post itself?

I have a very similar problem I'm looking to solve (calcualate the total number of people dialled into a conference call per minute given a call start time and call end time per conference line as per the below data table)

 Conference line ID Country Attendees Entry Exit 3859 US 1 17:08:55 17:51:56 1164 US 10 16:58:04 17:52:26 4282 US 1 17:06:28 17:38:13 1301 US 8 17:18:42 17:52:59 3039 US 1 17:07:39 17:52:13 2556 US 1 17:06:09 17:52:21 3031 UK 5 16:59:32 17:52:46 1479 UK 1 17:02:40 17:52:19 3929 UK 4 17:01:12 17:52:27 4271 UK 1 17:12:16 17:52:23 1962 UK 1 17:06:36 17:26:01 1622 UK 1 17:09:26 17:19:46

Kind regards

Simon

Super User III

Hi,

Thank you.  My solution there will not solve this problem because we had date entries there and in your we have time entries.  I do not know how to increment time entries.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

I *think* what you might want here is a disconnected Calendar table. Create you calendar table, Calendar, using CALENDAR or CALENDARAUTO. Use this as your Slicer (Between) or you could use filters.

Now, create the following measures:

```m_MaxCalendar = MAX(Calendar[Date])

m_MinCalendar = MIN(Calendar[Date])

m_Count = CALCULATE(COUNTROWS(CallsTable),FILTER(CallsTable, CallsTable[CallSubmitDate < m_MinCalendar && (CallsTable[CallResolvedDate] >= m_MaxCalendar || ISNULL(CallsTable[CallResolvedDate)))```

Something along those lines, create a Table visualization with your Date from your Calendar table and m_Count.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Helper I

Thanks very much smoupre for the reply, I realize that while going through your solution, in trying to simplify the data for posting here I've oversimplified and left out something important. I've now added this to the original post:

I need to have the measure calculate on the the CallsTable because there are many other factors on that callsTable that I want to apply slicers or filters on to change the CurrentlyOpen number. I might filter based on which team the call was resolved by, which city the call was resolved in, and many other factors (About 25 other fields in the table. ) Can this be done?

Best,

Ben

Announcements