cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bengisby Helper I
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

 

Thanks very much for any help you can provide.

Best,

Ben

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Super User IV
Super User IV

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.


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

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

bengisby Helper I
Helper I

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

bengisby Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bengisby Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bengisby Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors