cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAndes
Advocate III
Advocate III

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


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

26 REPLIES 26
alikhan_3
Frequent Visitor

We have similar case.

We need to select the most recent value based on the latest DateTime and DIA value. can someone give us the idea to resolve this?

pic2.jpgpic1.jpg

 

Hi@alikhan_3 ,

 

Can you share what is the exact ouput you require based on the table provided.

 

Can you post the data to One Drive / Google Drive and share the link here to find a solutiom.

 

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
felique
Frequent Visitor

Help pelase, the column is not avalaible in real time dataset, i am getting stock becouse i need the last record of  x types.... 

Vishwanathraoyl
Frequent Visitor

@CheenuSing The below code works well for my requirement:

KPI= COUNTROWS (

    FILTER (

        ADDCOLUMNS (

            CALCULATETABLE (

                SUMMARIZE (

                    'Table1',

                    'Table1'[ID],

                    Table1[SalesRepName],

                    "Rep",Table1[SalesRepName],

                    "POP", 'Table1'[ID],

                    "Latest", MAX ( 'Table1'[Date] )

                ),Table1[KPI] in {0,1}

            ),

            "Response", LOOKUPVALUE (

                'Tabke1'[KPI],

                'Table1'[ID], [POP],

                Table1[SalesRepName],[Rep],

                'Table1'[ActivationStandard],1,

                'Table1'[Date], [Latest]

            )

        ),

        [Response]

    )

)

 

I basically wanted to make it so that the "Latest" is dynamic by the date filter as I'm building a dynamic reporting on a few surveys which should ideally not require republishing. This makes it so.

Jsimonson
Regular Visitor

Easier way found when I could not figure out how to do the other things people wrote:

 

To Display results on the report that match rules based on a date... 

Edit Query - Add column tab - Select Date - Filter date by rule

 date column.jpgdate filter.jpg

robertlowens266
Frequent Visitor

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"

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

Hi @robertlowens266

 

We can do that in Power Query as well.

 

The approach is to find the MaxDate grouped by Name. And then add a column IsLatest by comparing the date and MaxDate .

derived. My test table had only 3 coulmns Date,Name and Response and 13000 rows.

 

The code is as under

 

 

let
    Source = Excel.Workbook(File.Contents("C:\PowerBICommunity\SampleData.xlsx"), null, true), 
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"ChangedType" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Name", type text}, {"Response", type text}}),

 

/* First Group by Name and Arrive at the MaxDate by Name */


    GroupbyName = Table.Group(ChangedType, {"Name"}, {{"MaxDate", each List.Max([Date]), type date}}),

 

/*  Do a selfJoin with the all records previously loaded using Name as common field */


    #"Merged Queries" = Table.NestedJoin(ChangedType,{"Name"},GroupbyName,{"Name"},"GroupedOnLetter",JoinKind.LeftOuter),

 

/* Expand the merged Table to select only the MAx Date */


    #"Expanded GroupedOnLetter" = Table.ExpandTableColumn(#"Merged Queries", "GroupedOnLetter", {"MaxDate"}, {"GroupedOnLetter.MaxDate"}),

 

/* Rename the column */


    #"Renamed Columns" = Table.RenameColumns(#"Expanded GroupedOnLetter",{{"GroupedOnLetter.MaxDate", "MaxDate"}}),

 

/* Lastly add the custom column IsLAtest by doing the comparision */


    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "IsLatest", each if [MaxDate] = [Date] then "Yes" else "No")

 

in
    #"Added Conditional Column"

 

 

I tested this on a 13000 rows in excel and it is very fast.

 

Test it out and feedback. And if it works please do give KUDOS.

 

Cheers

 

CheenuSing

 

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

Proud to be a Datanaut!
TMock
Frequent Visitor

Great solution.  Thanks!

Worked great! Thanks!

tringuyenminh92
Memorable Member
Memorable Member

Hi @LAndes,

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

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
Community Champion
Community Champion

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!
JohnH
Frequent Visitor

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
Regular Visitor

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

 

Thanks again to you all!

xharx
Regular Visitor

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])))

 

 

@CheenuSing Is there any way to make the IsLatest work according to slicers?

As in if I have selected April, IsLatest is applied only to April and if I have selected a year the IsLatest is applied to the whole year?

Hi @Vishwanathraoyl

 

Sorry for the delayed response.  Can you please put some data and the output expected on google drive or one drive and share the linke here.  This will enable me understand your requirement better and provide a soultion.

 

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
Anonymous
Not applicable

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!

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors