Customer Retention - Original Location

[vr1307]

Hello,

New PBI user here and I'm having some issues getting a DAX calculation to work as expected for some retention analysis.  I can appropriately calculate the beginning customers and the number of those still active as of a given start/end range.

The issue I have is on deriving the original location for that customer.  Our retention analysis needs to group the customer based on the location as of the first date.  A customer can move locations between the start/end range.  That's where things are breaking down for me and I'm not sure how exactly to address it.  I've tried a ton of different DAX functions and none of them get me where I need, so I'm in need of some direction.

This is an example of the equivalent SQL that we use in SSRS to give you an idea:

DECLARE @dteStart DATE = '02/28/2018'
DECLARE @dteEnd DATE = '06/30/2018'

SELECT
orig.[Branch Name]
, StartMembers = COUNT(*)
, RetainedMembers = COUNT(cur.EDWCustomerID)
, RetentionPer = CAST(COUNT(cur.EDWCustomerID) AS DECIMAL(12, 2)) / COUNT(*)
FROM Member.vwMemberCount orig
LEFT JOIN Member.vwMemberCount cur
ON orig.EDWCustomerID = cur.EDWCustomerID
AND cur.[Date of Report] = @dteEnd
AND cur.[Activity Type] = 'Current'
WHERE orig.[Date of Report] = @dteStart
AND orig.[Activity Type] = 'Current'
GROUP BY orig.[Branch Name]
WITH ROLLUP

Here's a data table for use in PBI for testing:

Member Count = 
DATATABLE
(
"Date of Report", DATETIME,
"Activity Date", DATETIME,
"Activity Type", STRING,
"Activity Count", INTEGER,
"EDWCustomerID", INTEGER,
"Branch Name", STRING,
{
{ "01/01/2018", "01/01/2018", "Current", 1, 42824, "Encinitas" },
{ "01/31/2018", "01/31/2018", "Current", 1, 42824, "Encinitas" },
{ "02/28/2018", "02/28/2018", "Current", 1, 42824, "Encinitas" },
{ "03/31/2018", "03/31/2018", "Current", 1, 42824, "Encinitas" },
{ "04/30/2018", "04/30/2018", "Current", 1, 42824, "Encinitas" },
{ "05/31/2018", "05/31/2018", "Misc", 1, 42824, "Carlsbad" },
{ "05/31/2018", "05/31/2018", "Current", 1, 42824, "Carlsbad" },
{ "05/31/2018", "05/31/2018", "Misc", -1, 42824, "Encinitas" },
{ "06/30/2018", "06/30/2018", "Current", 1, 42824, "Carlsbad" },
{ "07/31/2018", "07/31/2018", "Current", 1, 42824, "Carlsbad" },
{ "08/31/2018", "08/18/2018", "Current", 1, 42824, "Carlsbad" },
{ "01/01/2018", "01/01/2018", "Current", 1, 295048, "Carlsbad" },
{ "01/31/2018", "01/31/2018", "Current", 1, 295048, "Carlsbad" },
{ "02/28/2018", "02/28/2018", "Current", 1, 295048, "Carlsbad" },
{ "03/31/2018", "03/31/2018", "Current", 1, 295048, "Carlsbad" },
{ "04/30/2018", "04/30/2018", "Current", 1, 295048, "Carlsbad" },
{ "05/31/2018", "05/31/2018", "Current", 1, 295048, "Carlsbad" },
{ "06/30/2018", "06/06/2018", "Misc", 1, 295048, "Encinitas" },
{ "06/30/2018", "06/06/2018", "Misc", -1, 295048, "Carlsbad" },
{ "06/30/2018", "06/30/2018", "Current", 1, 295048, "Encinitas" },
{ "07/31/2018", "07/31/2018", "Current", 1, 295048, "Encinitas" },
{ "08/31/2018", "08/18/2018", "Current", 1, 295048, "Encinitas" },
{ "01/01/2018", "01/01/2018", "Current", 1, 435420, "Clairemont" },
{ "01/31/2018", "01/31/2018", "Current", 1, 435420, "Clairemont" },
{ "02/28/2018", "02/15/2018", "Misc", -1, 435420, "Clairemont" },
{ "02/28/2018", "02/15/2018", "Misc", 1, 435420, "Encinitas" },
{ "02/28/2018", "02/28/2018", "Current", 1, 435420, "Encinitas" },
{ "03/31/2018", "03/31/2018", "Current", 1, 435420, "Encinitas" },
{ "04/30/2018", "04/30/2018", "Current", 1, 435420, "Encinitas" },
{ "05/31/2018", "05/31/2018", "Current", 1, 435420, "Encinitas" },
{ "06/30/2018", "06/30/2018", "Current", 1, 435420, "Encinitas" },
{ "07/31/2018", "07/31/2018", "Current", 1, 435420, "Encinitas" },
{ "08/31/2018", "08/18/2018", "Current", 1, 435420, "Encinitas" },
{ "01/01/2018", "01/01/2018", "Current", 1, 494050, "Encinitas" },
{ "01/31/2018", "01/31/2018", "Current", 1, 494050, "Encinitas" },
{ "02/28/2018", "02/28/2018", "Current", 1, 494050, "Encinitas" },
{ "03/31/2018", "03/31/2018", "Current", 1, 494050, "Encinitas" },
{ "04/30/2018", "04/30/2018", "Current", 1, 494050, "Encinitas" },
{ "05/31/2018", "05/31/2018", "Current", 1, 494050, "Encinitas" },
{ "06/30/2018", "06/16/2018", "Lost", -1, 494050, "Encinitas" }
}
)

Begin count DAX function which works as expected:

Retention Begin = 
CALCULATE(
DISTINCTCOUNT('Member Count'[EDWCustomerID])
, FILTER(
'Member Count'
, 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MIN('Member Count'[Date of Report])
)
)

End count DAX function which works as expected:

Retention End = 
VAR RetentionBeginMembers = CALCULATETABLE('Member Count'
, 'Member Count'[Activity Type] = "Current"
, FIRSTDATE('Member Count'[Date of Report])
)
RETURN
CALCULATE(
DISTINCTCOUNT('Member Count'[EDWCustomerID])
, FILTER(
'Member Count'
, CONTAINS(RetentionBeginMembers, 'Member Count'[EDWCustomerID], 'Member Count'[EDWCustomerID])
&& 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MAX('Member Count'[Date of Report])
)
)

Calculated column function which works good for the begin date in the dataset only.  That makes sense to me as it's stored and not recalculated as part of the report processing.  That's the idea that I'm having trouble with.  I need this to move into a measure.  I need to be able to derive the original branch and later group on it in the reporting.

Retention Branch Calc = 
//This will be used in retention reporting to associate the activity to the original branch (first branch) 
CALCULATE(
FIRSTNONBLANK('Member Count'[Branch Name], 1)
, FILTER(
ALL('Member Count')
, 'Member Count'[EDWCustomerID] = EARLIER('Member Count'[EDWCustomerID])
&& 'Member Count'[Activity Type] = "Current"
)
)

I've tried to put this same calculated column into a measure in different variations and it works when the customer id is in the report but once I remove that and want the branch only the FIRSTNONBLANK or MINX breaks down.  That makes sense to me as it's now calculating over the entire dataset but I can't figure out how to get it to derive per each customer and then store that attribute for downstream processing to report/group.

 Here are my sample PBI results and the expected results from the SQL query:

 

[Anonymous · Accepted solution]

@vr1307 to get it for each customer just add this to your calc

ALLEXCEPT('Member Count', 'Member Count'[EDWCustomerID])

[vr1307]

Thanks @Anonymous.

That is actually the test measure calculation I had in the screenshot.  It works fine when the visual has the customer id but once I take that out and try to group on the branch it gives me the first non blank branch across the dataset not the first per each customer for grouping.

This is what I had.  Does that match with your recommendation?

Retention Branch =
//This will be used in retention reporting to associate the activity to the original branch (first branch)
CALCULATE(
FIRSTNONBLANK(Branch[Branch Name], 1)
, FILTER(
ALLEXCEPT('Member Count', 'Member Count'[EDWCustomerID])
, 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MIN('Member Count'[Date of Report])
)
)

[Anonymous]

@vr1307 to get by branch you need to create a new calc where instead of customer id in all except you use branch id. 

you could use a slicer to choose which measure to use in this case i.e.branch vs cust id

[vr1307]

Thank you for the help @Anonymous

I still can't get this to give the desired results.  I'm guessing I'm misunderstanding something about structuring this in the appropriate spot of the calculate.  Maybe I'm not following your instructions correctly but I've tried a number of ways.

This is the measure calculation I have now.  Moving the all except for the location id to see if I can get that but it still returns just 1 branch when it should be 2.  Should this ALLEXCEPT not be paired with the FIRSTNONBLANK?

Retention Branch =
//This will be used in retention reporting to associate the activity to the original branch (first branch)
CALCULATE(
FIRSTNONBLANK('Member Count'[EDWCostCenterID], 1)
, FILTER(
ALLEXCEPT('Member Count', 'Member Count'[EDWCostCenterID])
, 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MIN('Member Count'[Date of Report])
)
)

I tried this too:

Retention Branch =
//This will be used in retention reporting to associate the activity to the original branch (first branch)
CALCULATE(
FIRSTNONBLANK('Member Count'[EDWCostCenterID], 1)
, ALLEXCEPT('Member Count', 'Member Count'[EDWCostCenterID])
)

I also tried MIN type functions instead of FIRSTNONBLANK.

Like I mentioned before I can do a calcualted column and that works but it only works for the first date in the dataset.  I need a measure or need a way where a person can select any begin/end date and the calculation works from that.

[vr1307]

Alright, I got it working.  Thank you @Anonymous for getting me on track.

For anyone that stumbles across this later on, this is the final set of calculated columns and measures that ended up working for me.

Calculated column:

Retention Branch =
//This will be used in retention reporting to associate the activity to the original branch (first branch)
CALCULATE(
FIRSTNONBLANK(Branch[Branch Name], 1)
, FILTER(
ALLEXCEPT('Member Count', 'Member Count'[EDWCostCenterID])
, 'Member Count'[EDWCustomerID] = EARLIER('Member Count'[EDWCustomerID])
&& 'Member Count'[Activity Type] = "Current"
)
)

3 Measures - Count for begin/end and the retention percent:

Retention Begin =
CALCULATE(
DISTINCTCOUNT('Member Count'[EDWCustomerID])
, FILTER(
'Member Count'
, 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MIN('Member Count'[Date of Report])
)
)

Retention End =
VAR RetentionBeginMembers = CALCULATETABLE('Member Count'
, 'Member Count'[Activity Type] = "Current"
, FIRSTDATE('Member Count'[Date of Report])
)
RETURN
CALCULATE(
DISTINCTCOUNT('Member Count'[EDWCustomerID])
, FILTER(
ALL('Member Count')
, CONTAINS(RetentionBeginMembers, 'Member Count'[EDWCustomerID], 'Member Count'[EDWCustomerID])
&& 'Member Count'[Activity Type] = "Current"
&& 'Member Count'[Date of Report] = MAX('Member Count'[Date of Report])
)
)

Retention as % = DIVIDE([Retention End], [Retention Begin], 0)