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

     

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago
Hi LAndes,

Could you post your expression and data structure? So I quickly check it
LAndes's avatar
LAndes
Advocate III
9 years ago

Sure.. Here is an example:

 

Date Talken NameQ1.Q2Q3Q4
2/23/2016John SmithDisagreeDisagreeNeither agree or disagreeDisagree
2/22/2016John SmithAgreeAgreeAgreeAgree
9/23/2016Jones, NancyAgreeAgreeAgreeAgree
########Jones, RejaNeither agree or disagreeNeither agree or disagreeNeither agree or disagreeNeither agree or disagree
9/22/2016Anderson, ElaineNeither agree or disagreeAgreeAgreeNeither agree or disagree
9/6/2016Anderson, ElaineAgreeNeither Agree or DisagreeDisagreeNeither Agree or Disagree


See the issue?

  • 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

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

         

        It has to be a column and not a measure.

         

        Change it to a column and try again.

         

        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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Anonymous 

       

      How would you handle this if there was another variable?  Can you apply the filter for another category?

      For instance, if there were surveys for two different products that could be answered by the same set of respondents.  As in the original problem, each respondent could answer the survey multiple times. 

       

      Can you determine the most recent date that Responder 1 took the survey for Product A out of multiple submissions to Product A's survey, which is separart from the most recent date that Responder 1 took the survey for Product B out of multiple submissions to Product B's survey?

       

      So I would want to know:

      Survey Type:     

      Product A       Responder 1      Most recent date of Product A survey

      Product B        Responder 1      Most recent date of Product B survey

       

      Thanks in advance for your help!

       

      • ShrikantKhanna's avatar
        ShrikantKhanna
        Helper II
        8 years ago

        Hi Anonymous

         

        Is your query from 7/28 resolved?

        If not, here is what you can try - You can create a new column where you can CONCATENATE(ResponderName,Productname) and use it in EARLIER function per CheenuSing's suggestion. Or you can try adding two filters with EARLIER function.