Forum Discussion

jgray72's avatar
jgray72
Helper II
7 years ago
Solved

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.  Sty...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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?

jgray72's avatar
jgray72
Helper II
7 years ago

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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Is this your expected result?

     

    • jgray72's avatar
      jgray72
      Helper II
      7 years ago

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

       

      Thanks!

       

      Julie

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

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