Forum Discussion
Helper IIReturn Customers count within 2 years
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
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
5 Replies
- Jihwan_Kim
Super User
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] ) ) ))))RETURNIF (ISFILTERED ( 'Table'[Customer] ) && ISFILTERED ( 'Table'[Year] ),IF ( COUNTROWS ( VALUES ( 'Table'[Customer] ) ) > 0 && recenttwoyears > 0, 1 ))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
ManjunathaEPThank 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))RETURNIF(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.