Compare Date in a Group

[Limerick]

Hello together, 

I have this table with bookings:

OrderID	CustomerID	Status	Date	Lost / Won
1	1	Success	22.01.2021
2	1	Failed	10.01.2021
3	1	Success	01.01.2021
4	2	Success	05.01.2021
5	2	Failed	04.01.2021
6	2	Failed	04.01.2021
7	2	Success	01.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:

OrderID	CustomerID	Status	Date	Lost / Won
1	1	Success	22.01.2021	Won
2	1	Failed	20.01.2021	Won
3	1	Success	01.01.2021	Won
4	2	Success	16.01.2021	Won
5	2	Failed	15.01.2021	Won
6	2	Success	14.01.2021	Won
7	2	Failed	01.01.2021	Lost

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 🙂

[v-easonf-msft]

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 · Accepted solution]

@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"))

[Limerick]

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

[ryan_mayu]

you are welcome