Forum Discussion

ManjunathaEP's avatar
ManjunathaEP
Icon for Helper II rankHelper II
5 years ago
Solved

Return 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...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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

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's avatar
    ManjunathaEP
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        ManjunathaEP
        Icon for Helper II rankHelper II
        5 years ago

        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.