cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bengisby Regular Visitor
Regular Visitor

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

 

Thanks very much for any help you can provide.

Best,

Ben

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @bengisby,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png

11 REPLIES 11
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


bengisby Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

Hi @bengisby,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png

bengisby Regular Visitor
Regular Visitor

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

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
Super User

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

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

bengisby Regular Visitor
Regular Visitor

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

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
Super User

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

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.

bengisby Regular Visitor
Regular Visitor

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

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
Super User

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

Good to hear that.  Happy to help.