New, Renewed and Re-activated Customers Need Help!!!!

[ROCKYBI12]

• I need to categorize each customer to have a status for each fiscal year. I need to calculate new customer, renewed customer and reactivated customer.

New customer: First ever purchase in corresponding fiscal year.

Renewed Customer: Customer purchased in corresponding fiscal year and has made atleast one purchase in the year previous.

Re-activated Customer: Customer purchased in corresponding fiscal year and has made at least one purchase anytime before but not in the fiscal year pirior. 

Most solutions online only take into account using either previous month or # of days since last purchase. I have yet to find a solution that will work using FY (FYI FY is from my Date calender that I have added). I am open to any and all soultions. Thinking an IF formula could work. Thanks 

I cant share the data set but I have gave some examples below with the expected results (Status):

Fiscal Year	Date	Customer	Status
20-21	12/21/2021	A	Reactivated
17-18	12/21/2018	A	Reactivated
15-16	12/21/2016	A	New
20-21	12/21/2021	B	Renewed
19-20	12/21/2020	B	Reactivated
15-16	12/21/2016	B	New

[lbendlin]

That sample data is a good start but it is not sufficient. Please provide sanitized sample data that fully covers your issue. Include your fiscal year rules as well.

[ROCKYBI12]

Calendar =

VAR FirstFiscalMonth = 4 -- First month of the fiscal year

VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...

VAR FirstSalesDate = MIN ( 'Gift Data'[Gift Date] )

VAR LastSalesDate = MAX ( 'Gift Data'[Gift Date] )

VAR FirstFiscalYear = -- Customizes the first fiscal year to use

YEAR ( FirstSalesDate )

+ 1 * ( MONTH ( FirstSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)

VAR LastFiscalYear = -- Customizes the last fiscal year to use

YEAR ( LastSalesDate )

+ 1 * ( MONTH ( LastSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)

RETURN

GENERATE (

VAR FirstDay =

DATE (

FirstFiscalYear - 1 * (FirstFiscalMonth > 1),

FirstFiscalMonth,

1

)

VAR LastDay =

DATE (

LastFiscalYear + 1 * (FirstFiscalMonth = 1),

FirstFiscalMonth, 1

) - 1

RETURN

CALENDAR ( FirstDay, LastDay ),

VAR CurrentDate = [Date]

VAR Yr = YEAR ( CurrentDate ) -- Year Number

VAR Mn = MONTH ( CurrentDate ) -- Month Number (1-12)

VAR Mdn = DAY ( CurrentDate ) -- Day of Month

VAR DateKey = Yr*10000+Mn*100+Mdn

VAR Wd = -- Weekday Number (0 = Sunday, 1 = Monday, ...)

WEEKDAY ( CurrentDate + 7 - FirstDayOfWeek, 1 )

VAR WorkingDay = -- Working Day (1 = working, 0 = non-working)

( WEEKDAY ( CurrentDate, 1 ) IN { 2, 3, 4, 5, 6 } )

VAR Fyr = -- Fiscal Year Number

Yr + 1 * ( FirstFiscalMonth > 1 && Mn >= FirstFiscalMonth )

VAR Fmn = -- Fiscal Month Number (1-12)

Mn - FirstFiscalMonth + 1 + 12 * (Mn < FirstFiscalMonth)

VAR Fqrn = -- Fiscal Quarter (string)

ROUNDUP ( Fmn / 3, 0 )

VAR Fmqn =

MOD ( FMn - 1, 3 ) + 1

VAR Fqr = -- Fiscal Quarter (string)

FORMAT ( Fqrn, "\Q0" )

VAR FirstDayOfYear =

DATE ( Fyr - 1 * (FirstFiscalMonth > 1), FirstFiscalMonth, 1 )

VAR FirstDayFY =

DATE (

Fyr - 1 * (FirstFiscalMonth > 1),

FirstFiscalMonth,

1

)

VAR LastDayFY =

DATE (

Fyr * (FirstFiscalMonth > 1),

FirstFiscalMonth,

1

)

VAR Fydn =

SUMX (

CALENDAR ( FirstDayOfYear, CurrentDate ),

1 * ( MONTH ( [Date] ) <> 2 || DAY ( [Date] ) <> 29 )

)

RETURN ROW (

"DateKey", INT ( DateKey ),

"First Day in FY", FORMAT ( FirstDayFY, "dd mmm yyyy" ),

"Last Day in FY", FORMAT ( LastDayFY, "dd mmm yyyy" ),

"Sequential Day Number", INT ( [Date] ),

"Year Month", FORMAT ( CurrentDate, "mmm yyyy" ),

"Year Month Number", Yr * 12 + Mn - 1,

"Fiscal Year", "FY " & Fyr,

"Fiscal Year Number", Fyr,

"Fiscal Year Quarter", "F" & Fqr & "-" & Fyr,

"Fiscal Year Quarter Number", CONVERT ( Fyr * 4 + FQrn - 1, INTEGER ),

"Fiscal Quarter", "F" & Fqr,

"Month", FORMAT ( CurrentDate, "mmm" ),

"Fiscal Month Number", Fmn,

"Fiscal Month in Quarter Number", Fmqn,

"Day of Week", FORMAT ( CurrentDate, "ddd" ),

"Day of Week Number", Wd,

"Day of Month Number", Mdn,

"Day of Fiscal Year Number", Fydn,

"Working Day", IF ( WorkingDay, "Working Day", "Non-Working Day" )

)

)

[lbendlin]

Your calendar table should ideally be independent(ish) of the fact table. For Time Intelligence calculations you want it to contain full (fiscal) years.  My recommendation would be that you maintain your calendar table outside of Power BI.  Could be as simple as an Excel file in a Sharepoint.  That way you can also accommodate holidays in your "Working Day" column.  Let's hope that your customers are all in countries with the same workweek structure and the same holidays...

[ROCKYBI12]

a

[lbendlin]

Thank you for providing the sample data.

Your logic can be implemented with combinations of EXCEPT and INTERSECT in your measures:

New customer = 
var tfy = selectedvalue('Calendar'[FY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]<tfy)
return countrows(except(tc,pc))

Renewed Customer = 
var Pfy = selectedvalue('Calendar'[PFY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]=Pfy)
return countrows(intersect(tc,pc))

Reactivated Customer = 
var Pfy = selectedvalue('Calendar'[PFY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]=Pfy)
var puc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]<Pfy)
return countrows(intersect(except(tc,pc),puc))

See attached.  Also note how I created the FY and PFY (previous FY) columns:

Calendar = CALENDARAUTO(3)

FY = Format(edate('Calendar'[Date],-3),"YY-") & Format(edate('Calendar'[Date],+9),"YY")

PFY = Format(edate('Calendar'[Date],-15),"YY-") & Format(edate('Calendar'[Date],-3),"YY")

See attached.

[ROCKYBI12]

Thank you very much! Was hoping to have this as a coulmn with the text feilds so I could also show #of purchases for each status as well. Would your soultion also compute the number of purchases by status as well?

[lbendlin]

I don't understand what you are asking for. Can you show the expected outcome?

These calculations could theoretically be done as calculated columns but then you could not impact the results through any filters. You may not want that limitation?

[ROCKYBI12]

I am hoping to also have # of purchases by customer status. Same thing but instead of number of customers, it would return the number of purchases by customer status.

[lbendlin · Accepted solution]

You can use the same computations. Instead of Countrows() you use the Venn results as a filter on the amounts.

New customer Sales = 
var tfy = selectedvalue('Calendar'[FY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]<tfy)
return CALCULATE(sum(Sheet1[Amount]),except(tc,pc))

Renewed Customer Sales = 
var Pfy = selectedvalue('Calendar'[PFY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]=Pfy)
return CALCULATE(sum(Sheet1[Amount]),intersect(tc,pc))

Reactivated Customer Sales = 
var Pfy = selectedvalue('Calendar'[PFY])
var tc = values(Sheet1[Customer])
var pc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]=Pfy)
var puc = CALCULATEtable(values(Sheet1[Customer]),'Calendar'[FY]<Pfy)
return CALCULATE(sum(Sheet1[Amount]),intersect(except(tc,pc),puc))

Not sure if all numbers make sense though - feel free to validate