“Frequent Caller” metric

[JCovelli]

Does anyone know how I might use DAX to calculate our “Frequent Caller” metric here at work?… we say that anyone of our members who has called us more than one time in a week is a “Frequent Caller”… the data set is made of rows, each row is a call, with the date of the call. The DAX calculation would need to know if the caller has called previously, and within 7 days of the previous call… any ideas?

 

[ranman256]

sort the call list by CALLER,DATE,ELAPSED
this will run down the list,calculating elapsed time between calls.
filter on those <7

Public Sub MarkCallTime()
dim vCaller, vPrev
dim vDays, vPrevDate, vDate

  'sort calls by Caller,Date, Elapsed
range("A2").select
While ActiveCell.Value <> ""
   vCaller = activecell.value
   vDate = activecell.offset(0,1).value

   if vCaller <> vPrev then  
      vDays = 0
   else
      vDays = dateDiff("d",vPrevDate,vDate)  
   endif
   
   activecell.offset(0,2).value = vDays

   
   activecell.offset(1,0).select   'next row
   vPrev = vCaller
   vPrevDate = vDate
Wend
End Sub

 

[Ozeroth]

Hi JCovelli,

If you wanted to handle this through a measure, here's how I would approach it.

Let's say you have a Calls table with columns Date and Member.

I would look at each call a Member made, take the Date of that call, and if that same Member made at least one other call within +/-6 days of that call's date (i.e. the 7 days ending or starting on the Date of the call), I would treat that call as coming from a Frequent Caller.

In DAX, I would implement this by iterating through each Member's calls, and if 2 or more calls were made by that Member within +/-6 days, then that particular call would be treated as coming from a Frequent Caller.

A measure counting the number of calls from Frequent Callers would look something like this:

Calls from Frequent Callers = 
CALCULATE (
    [B]COUNTROWS ( Calls )[/B],
    FILTER (
        SUMMARIZE ( Calls, Calls[Date], Calls[Member] ),
        VAR CallDate = Calls[Date]
        RETURN
            CALCULATE (
                 // Within this CALCULATE call, a particular Member has been added to the filter context, along with days surrounding a particular call
                COUNTROWS ( Calls ),
                DATESBETWEEN ( Calls[Date], CallDate - 6, CallDate + 6 )
            )
            >= 2
    )
)

To instead count the number of distinct Frequent Callers, you would change COUNTROWS ( Calls ) to DISTINCTCOUNT ( Calls[Member] ).

I have used a forward & backward looking definition of Frequent Callers. Your original description sounded like just a backward looking definition. Just tweak the DATESBETWEEN(...) arguments to change this.

Let me know if that's close to what you were trying to do

Regards,
Owen