Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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/

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

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/

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?

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/

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.

Good to hear that.  Happy to help.


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

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 IDCountryAttendeesEntryExit
3859US117:08:5517:51:56
1164US1016:58:0417:52:26
4282US117:06:2817:38:13
1301US817:18:4217:52:59
3039US117:07:3917:52:13
2556US117:06:0917:52:21
3031UK516:59:3217:52:46
1479UK117:02:4017:52:19
3929UK417:01:1217:52:27
4271UK117:12:1617:52:23
1962UK117:06:3617:26:01
1622UK117:09:2617:19:46

 

Kind regards

Simon

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
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.