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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.