Forum Discussion
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?
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
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
Worked great! Thanks!