Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Customer Retention - Dynamic 12 Month Lookup Forward

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

 

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous ,

    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

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

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