Sequence Calculations

[sanc_152]

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 

[Ahmedx]

pls try this

[sanc_152]

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

[Ahmedx · Accepted solution]

pls try again this

Sequence Calculations3.pbix

[Ahmedx]

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.

[Ahmedx]

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")

[sanc_152]

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