Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic Last Visit based on Date Slicer

Hello Team,

 

I have a timeline slicer on the report and based on the period selected, I have a requirement to get the last visit date (max date)

 

So, if in the slicer I select 2 months, then i need to find last visit within that range, if it is Year selected then last visit of the year and so on. Can you please help in finding this dynamic last visit based on date slicer ?

 

thanks & regards,

Diwakar

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg,

      Thanks for your reply and thanks also for pointing to a good link.

      Sorry may be I didnt make it clear in the question but I want the Max Date to be used as a slicer as well. 

      I have a date slicer which is based on say column name = measurementdate.

      To get Max Visit Date working as a slicer, I am creating 2 calculated columns as follows :

       

      MaxVisitDate =
      CALCULATE(max(Measurement[measurementdate]);FILTER(Measurement;Measurement[hkpi_accountid]=EARLIER(Measurement[hkpi_accountid])))

       

      LastVisit = if(Measurement[MaxVisitDate]=Measurement[measurementdate];"Yes";"No")

       

      This shows the max measurement date per account ignoring the period selected in the date slicer. How do I get it show the max measurement date within the period selected and used it as a slicer ?

       

      Thanks !

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous

        After my test, you could try to use ALLEXCEPT Function to add measure instead of the two columns

        Measure = CALCULATE(MAX(Measurement[measurementdate]),ALLEXCEPT(Measurement,Measurement[hkpi_accountid]))
        Measure 2 = IF(CALCULATE(MAX(Measurement[measurementdate]),ALLEXCEPT(Measurement,Measurement[hkpi_accountid]))=CALCULATE(MAX(Measurement[measurementdate])),"Yes","No")

        Result:

        Best Regards,

        Lin