Forum Discussion

Mark_Adams's avatar
Mark_Adams
Frequent Visitor
2 years ago
Solved

Latest visit based on slicer date

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.

Need Help
  • Mark_Adams's avatar
    Mark_Adams
    2 years ago

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

7 Replies

  • Mark_Adams's avatar
    Mark_Adams
    Frequent Visitor
    2 years ago

    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])
    • Mark_Adams's avatar
      Mark_Adams
      Frequent Visitor
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • Mark_Adams's avatar
      Mark_Adams
      Frequent Visitor
      2 years ago

      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

       

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    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.

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    2 years ago

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