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 p...
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

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. 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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"
)

)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

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