topic Re: Finding the most recent value in DAX Commands and Tips

Finding the most recent value

LAndes — Fri, 20 Jan 2017 22:50:53 GMT

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

Re: Finding the most recent value

tringuyenminh92 — Sat, 21 Jan 2017 02:06:18 GMT

Hi LAndes,

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

Re: Finding the most recent value

LAndes — Sun, 22 Jan 2017 20:53:19 GMT

Sure.. Here is an example:

Date Talken	Name	Q1.	Q2	Q3	Q4
2/23/2016	John Smith	Disagree	Disagree	Neither agree or disagree	Disagree
2/22/2016	John Smith	Agree	Agree	Agree	Agree
9/23/2016	Jones, Nancy	Agree	Agree	Agree	Agree
########	Jones, Reja	Neither agree or disagree	Neither agree or disagree	Neither agree or disagree	Neither agree or disagree
9/22/2016	Anderson, Elaine	Neither agree or disagree	Agree	Agree	Neither agree or disagree
9/6/2016	Anderson, Elaine	Agree	Neither Agree or Disagree	Disagree	Neither Agree or Disagree

See the issue?

Re: Finding the most recent value

Anonymous — Mon, 23 Jan 2017 04:36:43 GMT

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

Re: Finding the most recent value

LAndes — Mon, 23 Jan 2017 15:45:51 GMT

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

Re: Finding the most recent value

Anonymous — Tue, 24 Jan 2017 04:38:51 GMT

Hi LAndes

It has to be a column and not a measure.

Change it to a column and try again.

Cheers

CheenuSing

Re: Finding the most recent value

LAndes — Tue, 24 Jan 2017 15:40:56 GMT

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

Re: Finding the most recent value

Anonymous — Fri, 28 Jul 2017 00:15:06 GMT

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!

Re: Finding the most recent value

ShrikantKhanna — Tue, 10 Oct 2017 09:33:36 GMT

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.

Re: Finding the most recent value

robertlowens266 — Tue, 13 Mar 2018 14:54:15 GMT

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"

Re: Finding the most recent value

Anonymous — Thu, 15 Mar 2018 03:40:06 GMT

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

Re: Finding the most recent value

robertlowens266 — Thu, 15 Mar 2018 13:08:28 GMT

Worked great! Thanks!

Re: Finding the most recent value

kohlivinayak — Wed, 30 May 2018 11:46:42 GMT

What if we want to calculate it dynamicaly, like if the date range is changed max date will now be recalculated.

Re: Finding the most recent value

Jsimonson — Mon, 02 Jul 2018 23:20:00 GMT

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

Re: Finding the most recent value

deeptiraj — Tue, 17 Jul 2018 06:42:59 GMT

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

Re: Finding the most recent value

Vishwanathraoyl — Wed, 18 Jul 2018 13:01:18 GMT

Anonymous 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?

Re: Finding the most recent value

Anonymous — Tue, 24 Jul 2018 04:09:26 GMT

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

Re: Finding the most recent value

Vishwanathraoyl — Tue, 24 Jul 2018 07:24:36 GMT

Anonymous 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.

Re: Finding the most recent value

JohnH — Wed, 08 Aug 2018 20:55:08 GMT

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

Re: Finding the most recent value

Anonymous — Tue, 04 Sep 2018 00:00:58 GMT

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