Latest visit based on slicer date

[Mark_Adams]

I have a dataset called 'CP Visits' with Case ID and Visit Date. Each case will have several visit dates. There is also a date slicer set to 'between' with dates supplied by a date dimension. I want to pull through the most recent visit for each case that happened before the max date on the date slicer. I can get the most recent visit but can't filter by the slicer.

Using MAX(DateDimension[Date]) in a coulumn just shows the highest date in the date dimension unless I do that as a measure.

[Mark_Adams · Accepted solution]

Thanks for all the input.

I everntually got it sorted myself using 

Most Recent Visit Date =

    CALCULATE(

        MAX('CP Visits'[Visit Date]),

FILTER(

    'CP Visits',

    'CP Visits'[Visit Date] <= [Max Date]

     && 'CP Visits'[Case ID] = SELECTEDVALUE('CP Visits'[Case ID])

)

    )

[Max Date] was from another measure 

= MAXX(ALLSELECTED(DateDimension[Date]), DateDimension[Date])

[CoreyP]

Filtered:

You were really close. Just change your MAX field to the fact table, rather than the date table. 

Last Visit = MAX( CPVisits[Date of Visit] )

[v-zhangti]

Hi, @Mark_Adams 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[3CloudThomas]

You don't have to, the measure will have a context filer from the slicer on the report page.

[Mark_Adams]

Unfortunately I can't get TOPN to work at all. It keeps telling me the expression refers to multiple values that cannot be converted to a scalar value

[3CloudThomas]

Try using TOPN in DAX Measure

TOPN DAX Function: How it works in Power BI? Comparison against the top group - RADACAD

[Mark_Adams]

that looks like a nice function but I can't work out how to restrict it to visits before the MAX(DateDimension[Date])