Return Data based on the most recent date

[ryan_b_fiting]

Hello Community - 

I am in need of some urgent assistance on a DAX issue I am having.  I am looking for a measure(s) that will return a patients last visit date and another that will return the patients last visit type.  

I need the to be dynamic so if I change a date range that I am looking at, it will then pull the new values based on the new date range.

Below is a very simple data set that I have from my appointment table:

EncounterID	PatientID	Visit Date	Visit Type
1	11	1/2/2022	Acu 60
2	22	1/3/2022	Acu 60
3	11	1/4/2022	Re Exam
4	22	1/4/2022	Acu 30
5	22	1/7/2022	Acu Wellness
6	33	1/9/2022	Acu Wellness

I would like the results to to be by patientid and this is what I would expect/hope the output to be when I show it in a table:

Patient ID	Last Visit	Last Visit Type
11	1/4/2022	Re Exam
22	1/7/2022	Acu Wellness
33	1/9/2022	Acu Wellness

Any help on how to calculate these measure would be greatly appreciated!

Thank You, Community!

Ryan F.

[v-yinliw-msft · Accepted solution]

Hi @ryan_b_fiting,

You can try this method:

New two measures:

Last Visit =
CALCULATE (
    MAX ( 'Appointment'[Visit Date] ),
    FILTER ( 'Appointment', 'Appointment'[PatientID] )
)

Last Visit Type =
CALCULATE (
    MAX ( Appointment[Visit Type] ),
    FILTER ( 'Appointment', 'Appointment'[Visit Date] = [Last Visit] )
)

Then in table visual:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoshP11]

Hello - @v-yinliw-msft 

I've just come across this solution and it looks to work for me as well however I'm wanting to try expand on it slightly. I need the measure to ignore any blanks if this is possible but struggling!

So where I have used TEST A, not always does this have a value and these to be ignored and to just show me the most recent date where the value of TEST A is not blank. Then in a visual I would want to display the value of TEST A.

Most Recent TEST A =

CALCULATE (

    MAX ('Data!'[TEST A]),

    FILTER ( 'Data!', 'Data!'[DATE-OF-TESTING] = [TEST A] )

)

[ryan_b_fiting]

This works great for one piece of the report that I am working on.  Now there is a branch off of this measure that I am trying to create with the same concept.  The only cavaet to this is I have a Relative Date filter that is for the next 7 days of future appointments (look ahead schedule for my providers), how can I create a new measure that will still give me the Patients last visit and last visit type prior to the 7 day look ahead schedule?