Adding a new Dax Column to show the next appointment date

[TonyGu]

Here is the appointment table I have.

I would like to add a column that shows the next appointment date. Below is how it should look like. Please give me some hints.

[tackytechtom · Accepted solution]

Hi @TonyGu ,

Does this one work for you?

Next Appointment Date = 
VAR _currentDate = Table[Appointment Date] 
RETURN
CALCULATE ( 
    MIN ( Table[Appointment Date] ), 
    ALLEXCEPT ( Table, Table[ClientID] ),
    'Table'[Appointment Date] > _currentDate 
)

You could also create an index to get the correct order, i.e. in Power Query, and then loop over them. Your AppointmentID might already exactly be that, then you could do something like this:

Next Appointment Date = 
VAR _currentIndex = Table[AppointmentID] 
RETURN
CALCULATE ( 
    MIN ( Table[Appointment Date] ), 
    'Table'[AppointmentID] = _currentIndex + 1 
)

Hope this gets you closer to a solution 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[TonyGu]

Hi Tom,

I have tried your dax and it works. Thanks a lot! Now I have another question. This time the table includes a status column. For those status is "False, I don't want to include that. The table should look like this. I wonder how to add that filter to the dax you have written.

I appreciate your help.

[TonyGu]

No worry. I figure out my self. Here is the DAX I wrote.

NPD =

VAR _currentDate = Appointment[Appointment Date]

RETURN

IF(Appointment[Status]="True",

CALCULATE (

MIN ( Appointment[Appointment Date] ),

ALLEXCEPT ( Appointment, Appointment[ClientID] ),

Appointment[Appointment Date] > _currentDate,

Appointment[Status]="True"

),BLANK())

[tackytechtom]

Awesome! Well done!

Don't forget to mark the answer to your initial question as a solution. Then it will be easier for others to find it 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Anonymous]

Hello,

How the next column should be related to the rest of the table ?

Can't you just add the information in the source table ?

Best Regards