Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

repeat customers in consecutive year

i need to find out only those customers who placed orders in subsequent years ( i.e. 2001 and 2002) , below is the sample data   example mohit placed orders in 2001 and 2002  vikas should be exclu...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi powerbiexpert22 ,

    I create a table as you mentioned.

    Then I create two new measures to satisfy your requirements.

    Measure =
CALCULATE (
    MAX ( 'Table'[shipdate] ),
    ALLEXCEPT ( 'Table', 'Table'[customerid], 'Table'[productid] )
)

    Measure 2 =
IF (
    DATEDIFF ( SUM ( 'Table'[orderdate] ), 'Table'[Measure], YEAR ) = 1,
    1,
    BLANK ()
)

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Read about EXCEPT and INTERSECT.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523