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.
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.
Solved! Go to Solution.
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.
Proud to be a Datanaut!
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?
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?
You are welcome. Please download my workbook and study my solution. I have remodelled your data in the backend to get the desired result.
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?
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.
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.