topic Re: Return Customers count within 2 years in DAX Commands and Tips

Return Customers count within 2 years

ManjunathaEP — Tue, 08 Jun 2021 13:16:31 GMT

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

Re: Return Customers count within 2 years

Jihwan_Kim — Tue, 08 Jun 2021 17:08:37 GMT

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

Re: Return Customers count within 2 years

ManjunathaEP — Wed, 09 Jun 2021 04:34:56 GMT

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

Re: Return Customers count within 2 years

Jihwan_Kim — Wed, 09 Jun 2021 04:46:14 GMT

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.

Re: Return Customers count within 2 years

ManjunathaEP — Wed, 09 Jun 2021 04:53:04 GMT

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.

Re: Return Customers count within 2 years

Jihwan_Kim — Wed, 09 Jun 2021 05:34:54 GMT

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