Forum Discussion
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
pls try again this
6 Replies
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")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
pls try this
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 pls try again this
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.
