Forum Discussion

LAndes's avatar
LAndes
Advocate 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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

JohnH's avatar
JohnH
Frequent Visitor
8 years ago

Thanks A LOT for this.  Very helpful, and thanks for the explanation along the way!

 

 

I could not get it to work, but after a lot of looking and researching, I noticed one parameter on the FILTER was left out by accident.

 

 

 

I got it to work with adding "yourtablename" as the first paramber in the FILTER:

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

  • xharx's avatar
    xharx
    Regular Visitor
    7 years ago

    Thanks! This worked immediatly, at first it gave me errors in Excel. Your addition helped me out.

     

    Thanks again to you all!

    • xharx's avatar
      xharx
      Regular Visitor
      7 years ago

      Sorry I am e newbie at this forum, I presumed "Reply" would reply to the post is was under.

       

      My previous post was meant as a reaction at JohnH

       

      For his addition:

       

      I got it to work with adding "yourtablename" as the first paramber in the FILTER:

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