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.

Anonymous's avatar
Anonymous
Not applicable
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.