cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAndes Frequent Visitor
Frequent Visitor

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 post survey is by the date taken.  I would like to create a "prepost Colunm that read the date and designated pre or post. I have tried a combination of If and Max but I can't seem to get it to work.. Any tips?

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
LAndes Frequent Visitor
Frequent Visitor

Re: Finding the most recent value


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?
@CheenuSing 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


 

View solution in original post

25 REPLIES 25
tringuyenminh92 New Contributor
New Contributor

Re: Finding the most recent value

Hi @LAndes,

Could you post your expression and data structure? So I quickly check it
LAndes Frequent Visitor
Frequent Visitor

Re: Finding the most recent value

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?

CheenuSing Super Contributor
Super Contributor

Re: Finding the most recent value

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
LAndes Frequent Visitor
Frequent Visitor

Re: Finding the most recent value


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?
@CheenuSing 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


 

View solution in original post

CheenuSing Super Contributor
Super Contributor

Re: Finding the most recent value

Hi @LAndes

 

It has to be a column and not a measure.

 

Change it to a column and try again.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
LAndes Frequent Visitor
Frequent Visitor

Re: Finding the most recent value

Thanks everyone. I added in a .Date into the formula and it worked. Most appreciative!

shart_wsol Frequent Visitor
Frequent Visitor

Re: Finding the most recent value

Hi @CheenuSing 

 

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!

 

Highlighted
ShrikantKhanna Regular Visitor
Regular Visitor

Re: Finding the most recent value

Hi @shart_wsol

 

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.

robertlowens266 Frequent Visitor
Frequent Visitor

Re: Finding the most recent value

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"

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 337 members 3,356 guests
Please welcome our newest community members: