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 p...
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.

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

    )