cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Frequent Visitor

Calculate days since last visit is not pulling from the last appointment date

The goal is to create a report displaying all patients with no contact based on a 3,6,9,12,18,24 month time period. This will help Mgt know how many patients are still marked active in the system though but have had no contact in a set time frame. The 90 day mark is a sign the patient can be marked inactive.

 

I created two new columns based off the Appointment Date field. One counts the days from the appointment till today and the second puts the appointment into a bucket.

 

My issue is that based on settings I never get to see ONLY the patients LAST appointment and how many months ago it was. 

The columns I created:

anwilkins_4-1632332660099.png

1) Days Conversion =

DATEDIFF(MERGE_PatientData[ApptStart], TODAY(),DAY)
 2) Time Groups =
IF('MERGE_PatientData'[Days Conversion]<=90,"1-3 Mo",
IF(MERGE_PatientData[Days Conversion]<=120, "4-6 Mo",
IF(MERGE_PatientData[Days Conversion]<=270, "7-9 Mo",
IF(MERGE_PatientData[Days Conversion]<=365, "10-12 Mo",
IF(MERGE_PatientData[Days Conversion]<=485, "13-18 Mo",
IF(MERGE_PatientData[Days Conversion]<=730, "19-24 Mo",
IF(MERGE_PatientData[Days Conversion]>=731, "25 Mo+")))))))

The PBI visual settings:

Patient ID = Latest

Pathient Name = Dont Summarize

Appointment Date = Latest

Time = First (display will change based on all of these settings)

Results for 3 patients look like this:

anwilkins_1-1632331833337.png

____BURN should have been in the 1-3 Mo bucket

____White, HERNANDEZ and AYEZ should all have been in the 25Mo+ bucket

Changing the date to Earliest give this result

anwilkins_2-1632332161482.png

And changing the date to Dont Summarize displays every appointment the person has had and the time period since the appt

anwilkins_3-1632332267988.png

Any help in getting it to display the name / true last visit / correct time frame is greatly appreciated.

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @anwilkins 

 

Download this PBIX file which contains the following code/solution

 

Try using a Measure rather than a column to work out the Latest Appointment, like this

 

Latest Appt = CALCULATE(MAX([ApptStart]), FILTER('MERGE_PatientData', [Name] = SELECTEDVALUE(MERGE_PatientData[Name])))

 

and calculate the Time Frame like this

 

TimeFrame = 

VAR _TimeSince = DATEDIFF([Latest Appt], TODAY(),DAY)

RETURN

SWITCH (TRUE,

_TimeSince <=90,"1-3 Mo",
_TimeSince <=120, "4-6 Mo",
_TimeSince <=270, "7-9 Mo",
_TimeSince <=365, "10-12 Mo",
_TimeSince <=485, "13-18 Mo",
_TimeSince <=730, "19-24 Mo",
"25 Mo+"

)

 

 

Giving this result.

timeframes.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
anwilkins
Frequent Visitor

Worked perfectly! Thank you so much!

PhilipTreacy
Super User
Super User

Hi @anwilkins 

 

Download this PBIX file which contains the following code/solution

 

Try using a Measure rather than a column to work out the Latest Appointment, like this

 

Latest Appt = CALCULATE(MAX([ApptStart]), FILTER('MERGE_PatientData', [Name] = SELECTEDVALUE(MERGE_PatientData[Name])))

 

and calculate the Time Frame like this

 

TimeFrame = 

VAR _TimeSince = DATEDIFF([Latest Appt], TODAY(),DAY)

RETURN

SWITCH (TRUE,

_TimeSince <=90,"1-3 Mo",
_TimeSince <=120, "4-6 Mo",
_TimeSince <=270, "7-9 Mo",
_TimeSince <=365, "10-12 Mo",
_TimeSince <=485, "13-18 Mo",
_TimeSince <=730, "19-24 Mo",
"25 Mo+"

)

 

 

Giving this result.

timeframes.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!