Forum Discussion
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 excluded since he placed orders in 2001 and 2003
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.
2 Replies
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/1447523Hi 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.
