Identify unique identifiers that skipped a year

[tippytappy]

Hi,

I'm new to PowerBI and would require some help on this issue that I have. I have a table with unique customer IDs (CustID), start date of product (Start_Date), end date of product (End_date) from 2014 to 2019. Product usually expires within a year and customers have to repurchase a year later. How can I create a column to identify customers who have purchased the product, stopped purchasing and purchased again afterwards? Is it also possible to identify the number of years that these customers stopped for?

For instance, customer A that purchased the product in 2015 & 2016, did not repurchase in 2017 but continued again in 2018?

Thanks!!

[Jihwan_Kim]

Hi, @tippytappy 

If I understood your question correctly, I think your fact table has more than three columns (Customer ID + StartDate + EndDate). For instance, order id columns and so forth.

Can you share your sample pbix file's link here? Then I can try to look into it to come up with more accurate measures.

Thanks.

[tippytappy]

Hi,

Yes you are right please find sample data below:

Am unable to paste the table here but have attached a screenshot above.

Thanks!

[Jihwan_Kim · Accepted solution]

Hi, @tippytappy 

Thank you for sharing.

Please check the below picture and the sample pbix file's link down below whether it is what you are looking for. It is for creating new columns.

Or, please share the idea about how you want to see the desired result.

Is there a gap? =

VAR currentorderid = 'Table'[OrderID]

VAR previousorderid =

MAXX (

FILTER (

'Table',

'Table'[OrderID] < currentorderid

&& 'Table'[CustomerID] = EARLIER ( 'Table'[CustomerID] )

),

'Table'[OrderID]

)

VAR previousenddate =

CALCULATE (

SELECTEDVALUE ( 'Table'[EndDate] ),

FILTER ( ALL ( 'Table' ), 'Table'[OrderID] = previousorderid )

)

RETURN

IF (

NOT ISBLANK ( previousenddate ),

IF ( 'Table'[StartDate] = previousenddate, "N", "Y" )

)

How long is a gap (days)? =

VAR currentorderid = 'Table'[OrderID]

VAR previousorderid =

MAXX (

FILTER (

'Table',

'Table'[OrderID] < currentorderid

&& 'Table'[CustomerID] = EARLIER ( 'Table'[CustomerID] )

),

'Table'[OrderID]

)

VAR previousenddate =

CALCULATE (

SELECTEDVALUE ( 'Table'[EndDate] ),

FILTER ( ALL ( 'Table' ), 'Table'[OrderID] = previousorderid )

)

RETURN

IF (

NOT ISBLANK ( previousenddate ),

DATEDIFF ( previousenddate, 'Table'[StartDate], DAY )

)

https://www.dropbox.com/s/g1aqmzx2gc5v4yj/tippytappy.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