cancel
Showing results 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

Helper I

## Sequence Calculations

Hi All,

Need one help

 Product Customer Week Date Flag 1 A 1 01-01-2023 Y 1 A 2 08-01-2023 Y 1 A 3 16-01-2023 Y 1 B 1 01-01-2023 Y 1 B 2 08-01-2023 Y 1 B 3 16-01-2023 Y 1 B 4 24-01-2023 N 2 B 4 08-01-2023 N 2 B 4 16-01-2023 N 2 B 4 24-01-2023 N

I want to calculate a Flag where product and week combination appearing in 3 consecutive weeks for different customer.

For ex.In above  Data product 1 and weeks 1,2 ,3 combinations appearing for customer A and B for 3 parallel or we can say in consecutive weeks .SO in the FLag column I have marked this as flag As Y since at the same time 1 product available for differnt customer for the same time period and it should be more than 2 weeks.

Is there any way we can calculate this in PBI using Dax calculated column or using measure

### @Ahmedx

@amitchandak

@frederikdeclerc

@lbendlin

@pratyashasamal

1 ACCEPTED SOLUTION
Super User

pls try again this

Sequence Calculations3.pbix

6 REPLIES 6
Super User

pls try this

Helper I

hi @Ahmedx

It is giving some differnt results

In below  data for Product A having 2 customers 1,2 resp and if you see they both are

appeared in consecutive weeks (4,5,6) So my flag should assigned as Y, but the formula retunring different result.
for both customers the product is overlaped for parallel period 4,5,6 So it should be giving me flag As Y.

 PRODUCT Customer Week Sequnce_ Flag_test A 2 1 1 N A 2 1 1 N A 2 2 1 N A 2 3 1 N A 2 4 1 N A 2 5 1 N A 2 6 1 N A 2 7 1 N A 2 8 1 N A 2 9 1 N A 2 10 1 N A 2 11 1 N A 1 4 4 N A 1 5 4 N A 1 6 4 N A 1 7 4 N A 1 8 4 N A 1 12 4 N A 1 13 4 N A 1 14 4 N
Super User

pls try again this

Sequence Calculations3.pbix

Super User

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

pls try this

``````Flag Column =
VAR t1 = [Product]
VAR t2 = [Customer]
VAR t3 = [Week]
VAR _tbl = SELECTCOLUMNS(ADDCOLUMNS( GENERATESERIES(1,53), "Step", [Value]&","&[Value]+1 &","&[Value]+2),"Step",[Step])
VAR _Result = CONCATENATEX(FILTER(ALL('Table'),'Table'[Product]=t1&&'Table'[Customer]=t2),[Week],",")
RETURN
IF(MAXX(FILTER(_tbl, CONTAINSSTRING(_Result,[Step])),[Step]), "Y","No")``````

Helper I

Hi @Ahmedx

Thanks for your hardwork and prompt reponse.
the Dax you have given to me it is only finding the patern whether it available or not .but my requirement is little differnt.

in the screenshot above for product 1 there are 2 customers a,b respec. and we are sending the same product to 2 cutsomers (a,b)within the same time frame week1,2,3 and it ismore than 2 weeks .So I want to consider only those product and customer with ahving same time frame and assign a flag As Y

Hoppefully  now it is clear .

Best Regards

san

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors