Customer Retention - Dynamic 12 Month Lookup Forward

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