Forum Discussion
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
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
28 Replies
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!
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
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
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?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
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.
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?
Hialikhan_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
Hi All,
This looks to be working from a data perspective. However I am getting something weird happening when I bring this into a visualisation.
You can see from my from the below that the data is working as intended with the formula's I have. For the purpose of my report I have added an additional filter so that it gives me the latest date for each candidateID on each joborderid as I can have multiple candidates working on different jobs.My issue is though, when I bring this into a table visualisation, both records appear with a value of 1 in column IsLatest and I am struggling to understand why it would be doing this?
Any help would be appreciated.
Tom
