topic Adding a new Dax Column to show the next appointment date in DAX Commands and Tips

Adding a new Dax Column to show the next appointment date

TonyGu — Wed, 09 Feb 2022 20:18:28 GMT

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.

Re: Adding a new Dax Column to show the next appointment date

Anonymous — Wed, 09 Feb 2022 20:48:20 GMT

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

Re: Adding a new Dax Column to show the next appointment date

tackytechtom — Wed, 09 Feb 2022 21:02:34 GMT

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

Re: Adding a new Dax Column to show the next appointment date

TonyGu — Wed, 09 Feb 2022 21:54:22 GMT

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.

Re: Adding a new Dax Column to show the next appointment date

TonyGu — Wed, 09 Feb 2022 21:59:10 GMT

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())

Re: Adding a new Dax Column to show the next appointment date

tackytechtom — Wed, 09 Feb 2022 22:06:34 GMT

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