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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors