Return Customers count within 2 years

[ManjunathaEP]

Hello,

Thank you in advance for helping me with the below issue. Please find the sample data given below and I would like to calculate the count of return customers within 2 years from a previous purchase.

Ex: If customer 1st purchase dated 01/Jan/2016 and next purchase dated 01/Jul/2017... I should get the count of the customer as a return. 

Order No	Order Date	Customer	Rev Class
111	01-Jan-16	ABC	Apple
111	01-Jan-16	ABC	Orange
222	01-Jan-16	XYZ	Orange
333	10-Jul-16	ABC	Orange
444	01-Jul-17	YRP	Apple
555	25-Aug-17	ABC	Orange
666	10-Jul-18	XYZ	Orange
777	08-Aug-18	YRP	Apple
779	30-Sep-18	ABC	Orange
888	10-Sep-19	XYZ	Orange
888	10-Sep-19	XYZ	Apple
999	25-Sep-19	ABC	Orange

Expected count for Return Customers
	2016	2017	2018	2019
ABC	1	1	1	1
XYZ				1
YRP			1

Please do the needful

[Jihwan_Kim]

Hi, @ManjunathaEP 

Please check the below picture and the sample pbix file's link down below.

Expected Result Measure =

VAR currentcustomer =

MAX ( 'Table'[Customer] )

VAR recenttwoyears =

COUNTROWS (

CALCULATETABLE (

VALUES ( 'Table'[Customer] ),

FILTER (

ALL ( 'Table' ),

'Table'[Customer] = currentcustomer

&& 'Table'[Order Date] < MAX ( 'Table'[Order Date] )

&& 'Table'[Order Date]

>= DATE ( YEAR ( MAX ( 'Table'[Order Date] ) ) - 2, MONTH ( MAX ( 'Table'[Order Date] ) ), DAY ( MAX ( 'Table'[Order Date] ) ) )

)

)

)

RETURN

IF (

ISFILTERED ( 'Table'[Customer] ) && ISFILTERED ( 'Table'[Year] ),

IF ( COUNTROWS ( VALUES ( 'Table'[Customer] ) ) > 0 && recenttwoyears > 0, 1 )

)

https://www.dropbox.com/s/qyxu6eb39ic43ma/manjunathaEPv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[ManjunathaEP]

Thank you so much for your support, unfortunately, it did not work for me for all the years. It calculated only for 2016 and 2017 and no values after 2018. I inserted a new column and calculated another method like below... but this measure also not worked correctly for 2019... can u please help on this.

Measure I used to create new column:

New/Return<2years = VAR rule1=CALCULATE(DISTINCTCOUNT('Table'[Customer]),Filter(ALLEXCEPT('Table','Table'[Customer]),'Table'[Order Date]<earlier('Table'[Order Date])))

VAR Calc=CALCULATE(min('Table'[Order Date]),Filter(ALLEXCEPT('Table','Table'[Customer]),'Table'[Order Date]<earlier('Table'[Order Date])))

VAR rule2=if(NOT(ISBLANK(Calc)),DATEDIFF(Calc,'Table'[Order Date],DAY))

RETURN

IF(OR(rule1=0,rule2>730),"New","Return")

[Jihwan_Kim]

Hi, @ManjunathaEP 

Thank you for your feedback.

My formula is for creating a calculated measure, not a calculated column. The expected result that was shown in the initial question was a matrix type. So, I thought you wanted to see it by creating a measure, not a calculated column.

[ManjunathaEP]

Hi Jihwan, I am sorry for the confusion. Yes, I requested help on the calculated measure. Later, I was working on the other alternatives and I tried creating a calculated column. If possible, request you to help and correct me the calculated column measure.

You are always supportive, thank you so much for your guidance.

[Jihwan_Kim · Accepted solution]

Hi, @ManjunathaEP 

Thank you for your feedback.

I am not 100% sure how your desired outcome looks like in the Calculated Column, but please check the below picture and the link down below.

Expected Result CC =
VAR currentorderno = 'Table'[Order No]
VAR currentdate = 'Table'[Order Date]
RETURN
IF (
COUNTROWS (
FILTER (
'Table',
'Table'[Customer] = EARLIER ( 'Table'[Customer] )
&& 'Table'[Order No] <> currentorderno
&& 'Table'[Order Date]
>= DATE ( YEAR ( currentdate ) - 2, MONTH ( currentdate ), DAY ( currentdate ) )
&& 'Table'[Order Date] < currentdate
)
) > 0,
"return",
"new"
)

https://www.dropbox.com/s/qyxu6eb39ic43ma/manjunathaEPv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM