Grouping by only sequential column values

[Anonymous]

Hello, 

I have a dataset that I would like to create different groups of data by collecting data based on column values in one main column but only while those values repeat without skipping a cell. Once the pattern is broken if the same value is seen again I would like for the grouping to restart.

For example:

Let's say I work for a fruit collecting company that only collects one type of fruit at a time in baskets that can hold 1000 fruit. We often switch what type of fruit we collect at random times and dates but we still only collect that one type of fruit.  We call these first 2 days of collecting apples "campaign #1" and the second time we begin collecting apples on 1/4/2021 we call that "campaign #2".

Date	Basket #	Fruit	Number Of Fruits Collected
1/1/2021	1	apple	1000
1/1/2021	2	apple	1000
1/2/2021	3	apple	800
1/2/2021	4	orange	200
1/4/2021	5	apple	1000

How could I group this data into campaigns using PowerBI?

[Jihwan_Kim]

Hi, @Anonymous 

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

all measures are in the sample pbix file.

https://www.dropbox.com/s/fx9e7eh0nujgjtx/tadams.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: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi this is very close to the solution I need but I have run into some errors. Firstly, the actual data I am trying to use contains letters and numbers instead of just letters like the words "apple" and "orange". Secondly, I would like to count campaigns for the other "fruit" being made as well. So in this case this would be campaign number one for oranges. I would also like to count the number of baskets within the campaign. with another column titled "basket number this campaign" that resets after each campaign.

Could you please help me with this?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Please share your sample pbix file, with some pictures of how your desired outcome looks like, then I can try to have a look into it.

Thank you.

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: https://www.linkedin.com/in/jihwankim1975/