Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sanc_152
Helper I
Helper I

Sequence Calculations

Hi All,

 Need one help

ProductCustomerWeekDateFlag
1A101-01-2023Y
1A208-01-2023Y
1A316-01-2023Y
1B101-01-2023Y
1B208-01-2023Y
1B316-01-2023Y
1B424-01-2023N
2B408-01-2023N
2B416-01-2023N
2B424-01-2023N

 

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

6 REPLIES 6
Ahmedx
Super User
Super User

pls try this

Screenshot_4.pngScreenshot_5.png

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.

PRODUCTCustomerWeekSequnce_Flag_test
A211N
A211N
A221N
A231N
A241N
A251N
A261N
A271N
A281N
A291N
A2101N
A2111N
A144N
A154N
A164N
A174N
A184N
A1124N
A1134N
A1144N

pls try again this

Sequence Calculations3.pbix

Ahmedx
Super User
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.

Ahmedx
Super User
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")

Screenshot_1.png

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.

sanc_152_2-1690871115199.png

 

 

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

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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