Customer Retention - Dynamic 12 Month Lookup Forward

[MKunnen]

Hi all,

I am attempting to calculate 12 month retenition rates and have been unable to find a solution in the forums. Here is a small example set below. Essentially, I want to say if Customer_ID 1 purchases in Jan 2018. Did they purchase again in the next 12 months? The brand is present as I would like to be able to filter and say if only Brand A is selected. Customer 1 is excluded. If A and B are selected, they do qualify for the 12 month window. 

The below code works assuming the LastDate is the same throughout the group but this assumption breaks quickly when looking at a quarterly view. Ideally, I would be able to look out from each Customer_ID's purchase date.

zMembers Next12 = 

VAR membersInNext12Month =
    CALCULATETABLE ( Orders, DATESBETWEEN('Date'[Date], DATEADD(LASTDATE('Date'[Date]), 5, DAY) , DATEADD(LASTDATE('Date'[Date]), 364, DAY)))
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Orders[Customer_ID] ),
        FILTER (
            Orders,
            CONTAINS (
                membersInNext12Month,
                Orders[Customer_ID], Orders[Customer_ID]
        )
    ))

Thanks,

Mark

[Ashish_Mathur]

Hi,

Share some sample data (which can be pasted in an Excel file) and show the expected result.

[Anonymous]

This can get a little indepth, but the guys over at sqlbi cover it extremely well. I'd say take a look at this link and see if it can help

https://www.daxpatterns.com/new-and-returning-customers/

[MKunnen]

Thanks for the reply. Are you able to explain what this means? I would think that this would never be true base on how I'm reading it.

'Date'[FullDate] < MIN ( 'Date'[FullDate] )

[New Customers] := 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sales[CustomerKey] ),
            "PreviousSales", CALCULATE (
                COUNTROWS ( Sales ),
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[FullDate] < MIN ( 'Date'[FullDate] )
                )
            )
        ),
        [PreviousSales] = 0
    )
)

[Anonymous]

Your thinking is correct, if you are looking at the whole table that will never be true since looking at the table as a whole. But everything happens in a context. So when you use that measure in, say a pivot table, you have context.  So it will compare the current date in the current filter context to that of the full date table. And that table is used to filter sales.  

It can get a little complex since you are bouncing around different contexts and such. I'd just try to look at the dax patterns site a little more and play around with the files (i think they have file) they have on there. 

[Anonymous · Accepted solution]

@MKunnen ,

I spent a little bit of time and put something together that you may find helpful.  I have attached a link to the pbix file below.  But going off your sample data ( I added a few records to test) here's the final matrix:

• Created a calendar table in Power Query, there's a small function I wrote to create it. 
• Created two dimension tables, Customer and Brand. These will be used to filter your sales table.  Here's the model:
• 
• So we will use Dates on rows and Customer ID and Brand ID as slicers coming from the new dimension table. 
• Here are the measures:

• First Order Date = 
  CALCULATE( 
      FIRSTDATE(Sales[OrderDate]), 
      ALL( DimCalendar)
  )

  Previous Day = 
  IF( 
      PREVIOUSDAY( DimCalendar[Date]) <= [First Order Date],
      BLANK(), 
      PREVIOUSDAY(DimCalendar[Date])
  )

  Date - 12 months = 
  VAR __CurrentDate = max( DimCalendar[Date])
  
  Var __FirstOrderDate=
      CALCULATE(
          FIRSTDATE(Sales[OrderDate]),
          ALL ( DimCalendar)
      ) 
  
  Var __Prev12MonthOrderDate=  
  CALCULATE(
     MAX( DimCalendar[Date]),
      DATEADD( DimCalendar[Date],-12,MONTH )
  )
  
  Var __CurrentDate_Equals_FirstDate= 
  __CurrentDate = __FirstOrderDate
  
  RETURN
  
  IF( 
      __CurrentDate_Equals_FirstDate, blank(), 
         IF (__Prev12MonthOrderDate < __FirstOrderDate, __FirstOrderDate,__Prev12MonthOrderDate)
  )

  I'm not going to explain each here, but will answer questions on them.  

Hope this helps!

Here's the file:

https://1drv.ms/u/s!Amqd8ArUSwDS0GnulEJ7Uie9BGuW

[Anonymous]

Realized I didnt add in the actual formula that displays the output in the table:

This order w/in 12 months of Previous? = 
IF( 
    AND(
        NOT( MAX(DimCalendar[Date]) = [First Order Date]),
        NOT( ISBLANK( [Distinct Client Count]) )
    )
 ,
 IF( 
     AND(
        HASONEVALUE( DimCalendar[Date]),
        CALCULATE(
            COUNTROWS(VALUES(DimCustomer[CustomerID])),
                CALCULATETABLE( Sales,
                    DATESBETWEEN( DimCalendar[Date], [Date - 12 months],[Previous Day])
                )
        ) 
        >0
     ), 
    "In TimeFrame"
)

)

[MKunnen]

No problem. I found it in your file. Thanks for all the time you spent with this. This gets me really close to what I need.

[Anonymous]

Glad it can help. I've been meaning to something like this on my end anyhow.