Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.