repeat customers in consecutive year

[powerbiexpert22]

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 excluded since he placed orders in 2001 and 2003

[Anonymous · Accepted solution]

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]

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

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/1447...