Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jgray72
Helper II
Helper II

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

 

You may download my revised PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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!

Hi,

 

Is this your expected result?

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Thanks!

 

Julie

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

You may download my revised PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much!  It works!!!  

 

Appreciate your help.

 

Julie

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashir - I ran into a problem after I continued to spot check the data.  The thing I didn't account for in the sample data, is that there can be several rows of data, as multiple regions can buy.

 

I think that's why the counts aren't correct below.  I don't think it's accurately counting the purchase instances at an aggregated level.  In this example, I should get one consecutive bought style and one non-consecutive bought style.  I should have made my sample data better reflect my raw data.

Capture.PNG

Hi,

 

So there is nothing wrong with my solution.  If your requirements keep changing, it is quite obvious that the solution would require tweaking.  Please do not be in a hurry to just post some sample which does not reflect yoru actual dataset.  Share a well thought over dataset, describe the business question in detail and then show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashir - new to DAX and Power BI, so learning a lot on the fly.  I will continue to tweak.  I've spent over 2 weeks trying to figure this out and I just can't nail it down.  I'm a business user vs. an IT person, so I'm not used to "writing" reports.  I don't know SQL, VBA, etc.  

Hi,

 

I am an end business user myself and have absolutely no knowledge about any programming language.  Whenever you post any question, please ensure that the dummy dataset resembles your actual dataset and you describe the business question very clearly so that the person helping you can come up with a robust solution.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks so much, it worked!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors