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
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"
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!
Great solution. Thanks!
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
