Forum Discussion
Figuring counts based on time frame.
So I figured this would be simple but the answer has alluded me. I have a table that holds call informaiton. One column is for the Telephone Number of the caller, one is the Queue the caller called into, and one is the Date the call took place. What I'm needing to do is find a way to count how many times a customer called back in within a three week period. So essentially if a customer had 2 or more calls happen within a three week period count it, otherwise exclude it. I haven't figured out how to accurately count only calls that have happend twice or more. I finally figured out how to count calls between dates but not sure if this is the correct path since this will count all calls instead of just the multiple onces. (BeginDate and EndDate are just datediff 3 weeks from all date in either direction)
TestingMeasure:=CALCULATE(COUNTROWS(F_QUEUE_CALL_DETAIL),FILTER(F_QUEUE_CALL_DETAIL, ([BeginDate] <= LASTDATE(D_DATE[Date])
&& [EndDate]>= FIRSTDATE(D_DATE[Date]))))
3 Replies
- v-xicaiCommunity Support
Hi Giant_Pygmy ,
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
- Giant_PygmyNew Member
The goal is to identify if a tel# has called back in within a certain timeframe (3 weeks) and if it has then to count it. Ultimately this will be used to figure out a resolution effectiveness KPI. The theory is if a customer had to call back in then the issue was not resolved.
Sample Data
CallDate Phone PhoneQueue 1/1/2019 789-555-0184 CustService 1/1/2019 344-555-0181 Billing 1/1/2019 264-555-0164 TechSupport 1/1/2019 727-555-0190 Billing 1/1/2019 273-555-0181 TechSupport 1/1/2019 429-555-0145 CustService 1/1/2019 334-555-0146 CustService 1/1/2019 927-555-0198 CustService 1/1/2019 221-555-0141 CustService 1/1/2019 539-555-0142 CustService 1/2/2019 206-555-0195 Billing 1/2/2019 250-555-0127 Billing 1/2/2019 661-555-0168 CustService 1/2/2019 426-555-0181 CustService 1/2/2019 159-555-0190 Billing 1/2/2019 292-555-0128 TechSupport 1/2/2019 987-555-0126 CustService 1/3/2019 397-555-0144 Billing 1/3/2019 119-555-0144 Billing 1/3/2019 652-555-0115 CustService 1/3/2019 383-555-0111 CustService 1/3/2019 752-555-0187 TechSupport 1/3/2019 428-555-0176 TechSupport 1/3/2019 884-555-0193 CustService 1/3/2019 773-555-0182 CustService 1/3/2019 542-555-0164 Billing 1/3/2019 941-555-0155 Billing 1/3/2019 140-555-0143 CustService 1/3/2019 945-555-0112 Billing 1/3/2019 539-555-0182 Billing 1/4/2019 889-555-0176 CustService 1/4/2019 859-555-0173 CustService 1/4/2019 119-555-0167 Billing 1/4/2019 115-555-0113 Billing 1/4/2019 937-555-0136 CustService 1/4/2019 680-555-0192 CustService 1/4/2019 219-555-0126 Billing 1/4/2019 839-555-0198 TechSupport 1/4/2019 551-555-0155 CustService 1/4/2019 188-555-0152 Billing 1/4/2019 767-555-0151 CustService 1/4/2019 394-555-0176 Billing 1/4/2019 820-555-0166 Billing 1/4/2019 358-555-0188 CustService 1/4/2019 819-555-0186 CustService 1/5/2019 175-555-0198 TechSupport 1/5/2019 752-555-0185 TechSupport 1/5/2019 572-555-0156 CustService 1/5/2019 138-555-0156 CustService 1/5/2019 143-555-0113 Billing 1/5/2019 147-555-0192 Billing 1/5/2019 835-555-0116 CustService 1/5/2019 722-555-0169 Billing 1/5/2019 576-555-0171 Billing 1/5/2019 156-555-0187 CustService 1/5/2019 706-555-0140 CustService 1/5/2019 754-555-0134 Billing 1/5/2019 899-555-0134 Billing 1/5/2019 169-555-0178 CustService 1/5/2019 698-555-0183 CustService 1/5/2019 429-555-0145 CustService 1/5/2019 916-555-0137 Billing 1/5/2019 158-555-0188 TechSupport 1/5/2019 362-555-0121 CustService 1/6/2019 539-555-0121 Billing 1/6/2019 962-555-0144 CustService 1/6/2019 716-555-0123 Billing 1/6/2019 674-555-0188 Billing 1/6/2019 186-555-0135 CustService 1/6/2019 847-555-0151 CustService 1/6/2019 425-555-0158 TechSupport 1/6/2019 185-555-0190 TechSupport 1/6/2019 691-555-0149 CustService 1/6/2019 834-555-0132 CustService 1/6/2019 962-555-0112 Billing 1/6/2019 753-555-0179 Billing 1/6/2019 554-555-0124 CustService 1/6/2019 156-555-0187 CustService 1/6/2019 252-555-0127 Billing 1/6/2019 581-555-0172 Billing 1/6/2019 153-555-0114 CustService 1/6/2019 957-555-0125 CustService 1/6/2019 941-555-0155 Billing 1/6/2019 329-555-0183 Billing 1/6/2019 475-555-0188 Billing - Giant_PygmyNew Member
A couple sample ideas on how to flag those telephone numbers:
Month/Que TelNumber DupFlag Jan CustService 429-555-0145 1 156-555-0187 1 Billing 941-555-0155 1 Queue Jan CustService 94% Billing 98% TechSupport 92%