Customer Status Calculation

[PowerRocky12]

I finally found a Dax calculation to create a column that gives me new, renewed, re-activated status for each customer. 

New: First Customer Purchase (Stays in status for entire FY)

Renewed: Customer repeats a purchase in Previous year and in that given year (Stays in status for entire FY)

Reactivated: Customer repeats a purchase in a pior year (Not in Previous year) and in that given year (Stays in status for entire FY)

The below Dax calculation gives me my desired results and works in my test data set but I have a very large data set and I keep running our of memory trying to load it. I think the earlier data function which is taking forever. Would anyone be able to provide some help for either getting this to work or tweaking the forumla so it will run on a large data set? This needs to be a column because this figure will be used for alot of my fields.

Status Test =

var earlier_date = CALCULATE(max('Table'[Date]), filter('Table','Table'[Date] < EARLIER('Table'[Date]) && 'Table'[Customer ID] = EARLIER('Table'[Customer ID])))

var fiscal_start = if('Table'[Date] >= DATE(year('Table'[Date]),4,1),DATE(year('Table'[Date]),4,1),DATE((year('Table'[Date])-1),4,1))

var fiscal_end = if('Table'[Date] >= (date(year('Table'[Date]),3,31)), date(year('Table'[Date]) +1,3,31),DATE(year('Table'[Date]),3,31))

var earlier_fiscal_start =if(earlier_date >= DATE(year(earlier_date),4,1) - 366, DATE(year(earlier_date),4,1) - 366, DATE((year(earlier_date)-1),4,1) - 366)

var earlier_fiscal_end =if(earlier_date >= (date(year(earlier_date),3,31)), date(year(earlier_date) +1,3,31),DATE(year(earlier_date),3,31))

var first_date = CALCULATE(FIRSTDATE('Table'[Date]),ALLEXCEPT('Table','Table'[Customer ID]))

var end_first_date = if(first_date >= (date(year(first_date),3,31)), date(year(first_date) +1,3,31),DATE(year(first_date),3,31))

var First_date_fiscal_start =if(first_date >= DATE(year(first_date),4,1) - 366, DATE(year(first_date),4,1) - 366, DATE((year(first_date)-1),4,1) - 366)

Return if(earlier_date = BLANK(),"New",

if(fiscal_end - end_first_date = 365 || fiscal_end = end_first_date|| earlier_fiscal_end = fiscal_end || DATEDIFF(earlier_fiscal_end,fiscal_end,YEAR) = 1 ,"Re-New", if(DATEDIFF(earlier_fiscal_end,fiscal_end,YEAR) > 1,"Re-Activated")) )

[DataInsights · Accepted solution]

@PowerRocky12,

It's recommended to use a variable instead of the EARLIER function.

https://dax.guide/earlier/ 

Also, for the variable first_date, you could create a separate table (using DAX or M) that has two columns: Customer ID, and First Date. Create a relationship between this table and the fact table, and use the RELATED function to retrieve First Date. This should be faster than calculating First Date for each row in the fact table.

[PowerRocky12]

Hey, I created a seperate table the other day with ID and year and it worked perfectly. Thank you replying!

[DataInsights]

Glad to hear that!