Forum Discussion

LAndes's avatar
LAndes
Icon for Advocate III rankAdvocate III
9 years ago
Solved

Finding the most recent value

Hi all, I have survey data with multiple people taking the survey mutiple times.  Each row is a survey response with a date taken. The only way I know which is the "pre survey" and which is the po...
  • LAndes's avatar
    LAndes
    9 years ago
    Thank you- I see the logic in your approach but I am having trouble with the synax for the "Earlier" section. Per your note, I did the following:
    MaxDate = CALCULATE(MAX('Community Leadership Assessment (2)'[Date Talken ], FILTER('Community Leadership Assessment (2)','Community Leadership Assessment (2)'[Name]=EARLIER('Community Leadership Assessment (2)'[Name]))))
    But i got this error-A single value for column 'Date Talken ' in table 'Community Leadership Assessment (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
    Any thoughts?
    Anonymous wrote:

    Hi LAndes

     

    Please try the following

     

    1. Create a column called MaxDate

        MaxDate =    

                     CALCULATE(MAX(yourtablename[yourDatecolumn]),FILTER(yourtablename[Name]=EARLIER(yourtablename[Name])))

     

    What this does is computes the MaxDate by Name and popluates in all records grouping by Name.

     

    2. Create a column called IsLatest

        IsLatest = If([Date]=[MaxDate],"Latest","Older")

     

    3. Now create a report with relevant columns from yourtablename and use IsLatest column as a Visual Level Filter filtered for "Latest".

     

    Check it out.

     

    If it works please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

     

robertlowens266's avatar
robertlowens266
Frequent Visitor
8 years ago

Is there any way this can be done in the "Edit Query" section rather than at the visualization step? I am looking to filter this in the query editor to create a unique list of values to create a relationship between tables. So, that needs to be done at the query level as compared to the visualization level. Thanks!

 

I attempted the Calculate formula listed above and the custom column editor gave me a "The name Calculate wasnt recognized"

deeptiraj's avatar
deeptiraj
New Member
8 years ago

Hi , But this will work only for latest date but i need to time travel, For example, I can select earlier dates then i need to see the on that particular date state how we can do, here the problem is it filter with latest event but when i am selecting some other dates as filter then it will now showing the event counts how we can achieve