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
Limerick
Frequent Visitor

Compare Date in a Group

Hello together, 

I have this table with bookings:

OrderIDCustomerIDStatusDateLost / Won
11Success22.01.2021 
21Failed10.01.2021 
31Success01.01.2021 
42Success05.01.2021 
52Failed04.01.2021 
62Failed04.01.2021 
72Success01.01.2021 

 

What I need to do now is to see, if a failed order is beeing rebooked within 7 days and if it was successfull.
So basically I have to compare every failed OrderID with the following orders within the group of CustomerID and see, if there was a booking within 7 days and if it was successfull:

OrderIDCustomerIDStatusDateLost / Won
11Success22.01.2021Won
21Failed20.01.2021Won
31Success01.01.2021Won
42Success16.01.2021Won
52Failed15.01.2021Won
62Success14.01.2021Won
72Failed01.01.2021Lost

 

OrderID 2 was Failed on 20.01. but later it was Won, because on 22.01. the Customer tried it again and went through.

OrderID 5 was Failed on 15.01. but later it was Won, because on 16.01. the Customer tried it again and went through.

Thank you in advance and best regards 🙂

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Limerick 

not sure why the status and date are different in the two tables, i used the second one.

Column = 
VAR _date=minx(FILTER('Table','Table'[CustomerID]=EARLIER('Table'[CustomerID])&&'Table'[Date]>EARLIER('Table'[Date])&&'Table'[Status]="Success"),'Table'[Date])
return if('Table'[Status]="Success","Won",if(_date-'Table'[Date]>7,"Lost","Won"))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi, @Limerick 

Could you please tell me whether your problem has been solved?

If yes, you could accept the helpful answer as solution. You could also share your own solution here.
For now, there is no content of description in the thread. If you still need help, please share more details to us.

 

Best Regards,
Community Support Team _ Eason

ryan_mayu
Super User
Super User

@Limerick 

not sure why the status and date are different in the two tables, i used the second one.

Column = 
VAR _date=minx(FILTER('Table','Table'[CustomerID]=EARLIER('Table'[CustomerID])&&'Table'[Date]>EARLIER('Table'[Date])&&'Table'[Status]="Success"),'Table'[Date])
return if('Table'[Status]="Success","Won",if(_date-'Table'[Date]>7,"Lost","Won"))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you very much - works perfect.
Yeah i changed the data and forgot to do it in the first table too 😉

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.