Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Icon for Post Patron rankPost Patron
3 years ago
Solved

Return Data based on the most recent date

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:

 

EncounterIDPatientIDVisit DateVisit Type
1111/2/2022Acu 60
2221/3/2022Acu 60
3111/4/2022Re Exam
4221/4/2022Acu 30
5221/7/2022Acu Wellness
6331/9/2022Acu 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 IDLast VisitLast Visit Type
111/4/2022Re Exam
221/7/2022Acu Wellness
331/9/2022Acu Wellness

 

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

Thank You, Community!

Ryan F.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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.

3 Replies

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.

    • ryan_b_fiting's avatar
      ryan_b_fiting
      Icon for Post Patron rankPost Patron
      3 years ago

      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?

    • JoshP11's avatar
      JoshP11
      Icon for Helper II rankHelper II
      3 years ago

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

      )