Get Next Record for the same ID

[bisartorelli]

Hi guys,

I'm kinda new in PBI and need help with this logic. 

I did something in M (following a youtube video), but its taking too long to join the tables and it is timing out in the service.

I want to know if you guys have another solution for this problem:

I need to identify if the same ID (Customer + Child) had a Rebook or not. In this case, I'll need to check if the same ID has a "next date".

This is the original data:

Customer Name	Child's Name	Appointment
AAAAA	XXXXX	9/8/2022
AAAAA	XXXXX	9/9/2022
AAAAA	YYYYY	10/1/2022
AAAAA	YYYYY	10/5/2022
BBBBB	XXXXX	11/2/2022
BBBBB	XXXXX	11/5/2022
BBBBB	XXXXX	11/7/2022
BBBBB	XXXXX	11/8/2022
BBBBB	YYYYY	12/5/2022

This is what I am trying to do - if Next Appointment > Appointment then Rebook else No Rebook. 

Customer Name	Child's Name	Appointment	Next Appointment	Result
AAAAA	XXXXX	9/8/2022	9/9/2022	Rebook
AAAAA	XXXXX	9/9/2022	NULL	No Rebook
AAAAA	YYYYY	10/1/2022	10/5/2022	Rebook
AAAAA	YYYYY	10/5/2022	NULL	No Rebook
BBBBB	XXXXX	11/2/2022	11/5/2022	Rebook
BBBBB	XXXXX	11/5/2022	11/7/2022	Rebook
BBBBB	XXXXX	11/7/2022	11/8/2022	Rebook
BBBBB	XXXXX	11/8/2022	NULL	No Rebook
BBBBB	YYYYY	12/5/2022	NULL	No Rebook

Could you pleeeeease help me with that? Any ideas?

[bisartorelli]

it worked!!!! Thank you so much!!

[lukiz84 · Accepted solution]

Hi,

2 calculated columns:

Next Appointment :=
      CALCULATE(
          MIN(YourTable[Appointment]),
          FILTER(
              YourTable,
              YourTable[Customer Name] = EARLIER(YourTable[Customer Name]) && 
              YourTable[Childs Name] = EARLIER(YourTable[Childs Name]) && 
              YourTable[Appointment] > EARLIER(YourTable[Appointment])
          )
       )

and

Result := 
   IF(
      ISBLANK(YourTable(Next Appointment]),
      "No Rebook",
      "Rebook"
   )