cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors