Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TonyGu
Helper I
Helper I

Adding a new Dax Column to show the next appointment date

Here is the appointment table I have.

TonyGu_0-1644437709612.png

 

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.

TonyGu_1-1644437872223.png

 

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
tackytechtom
Super User
Super User

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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.

TonyGu_1-1644443622723.png

 

I appreciate your help.

 

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

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.