cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jgray72 Frequent Visitor
Frequent Visitor

How Can I Count Breaks in a Pattern?

Hi - in the below data set, I want to count the number of styles that have a break in their buying pattern.  For example, I want to know that two out of the 4 styles don't have consecutive buys.  Styles 2 & 4 would need identified as have a break in their buy pattern.

 

I would then make a KPI that 2 out of the 4 styles don't have consecutive buys per our buy calendar.  50% of the styles are not bought consectutively.  Newer user, so I'm sure someone out there has the answer!

 

Capture.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Hi,

 

You may download my MS Excel file from here.  The same file can be imported into PBI desktop as well.

Highlighted
Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Hi,

 

You may download my revised PBI file from here.

 

Hope this helps.

 

Untitled.png

14 REPLIES 14
Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Well, you will certainly need to unpivot your date columns and almost certainly use EARLIER. What I am struggling with is the logic. So if something is bought once and never bought again, that is not a break in consequetive buying, only if something is bought, bought again, not bought and then bought again.

 

Also, are the dates presented just examples or would the dates really be every day? Or are these some kind of reporting period?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


jgray72 Frequent Visitor
Frequent Visitor

Re: How Can I Count Breaks in a Pattern?

Hi - thank you for replying.  Yes, if something is bought once, that is a different metric, that's a "one time buy."  The dates presented are per a buy date calendar, so sometimes there are gaps in between those dates.  I want to know for a vendor the following:

 

1) One time buys

2) Consecutive buys

3) Non-consecutive buys

 

I hope that helps explain what I'm looking for.  I figured out the one time buy metric, I just can't figure out what functions(s) will be needed to figure out those breaks in the buying pattern.

 

Thanks!

Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Hi,

 

Is this your expected result?

 

Untitled.png

jgray72 Frequent Visitor
Frequent Visitor

Re: How Can I Count Breaks in a Pattern?

Hi Ashish - that is indeed the expected result.  How were you able to achieve it?  

 

Thanks!

 

Julie

Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Hi,

 

You may download my MS Excel file from here.  The same file can be imported into PBI desktop as well.

jgray72 Frequent Visitor
Frequent Visitor

Re: How Can I Count Breaks in a Pattern?

Thanks so much, it worked!

jgray72 Frequent Visitor
Frequent Visitor

Re: How Can I Count Breaks in a Pattern?

Hi - I played with this formula some more and it doesn't work.  It doesn't tell the difference between a buying pattern without breaks vs. one that has breaks.  I coverted it to DAX and it just gives the number of buys that aren't one time buys.  Still working it, as the solution is proving to be evasive!

 

183012 should not show up below as having intermittent purchases.

 

Capture.PNG

Highlighted
Super User
Super User

Re: How Can I Count Breaks in a Pattern?

Hi,

 

You may download my revised PBI file from here.

 

Hope this helps.

 

Untitled.png

jgray72 Frequent Visitor
Frequent Visitor

Re: How Can I Count Breaks in a Pattern?

Thank you so much!  It works!!!  

 

Appreciate your help.

 

Julie