Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 🙂
Solved! Go to Solution.
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"))
Proud to be a Super User!
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
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"))
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
Proud to be a Super User!