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 Help
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