Forum Discussion
Check for previous year entry
Looked forever, but couldn't find the right thing to search...
We are looking to compare employee evaluations from year to year. However, we only want compare employees that have evaluations for both years. For example, if we were looking at 2018, we would only want to consider employees that had 2017 and 2018 evaluations. If looking at 2016, we'd only want employees that had 2015 and 2016.
I've come up with the idea to create a new column that would be a Y/N column. For the row that was the 2016 evaluation, it would look for a row with the same employee ID where year = 2015. If it exists, column = Y. If not, column = N.
Basically, look for rows with the same employee ID. Does one exist where Year = this rows year - 1.
Here's some simple example data.
First sheet is raw data. Second is what I want it to look like. Other sheets are what the data would look like filtered.
Thanks!
Hi Anonymous ,
I have created a sample for your reference, please check the following steps as below.
1. add a custom column as below.
= Table.AddColumn(#"Changed Type", "Custom", each [Year]+1)2. Self merge the table like that.
3. Expand the Eval Score column and add a custom column.
= Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y")4. Then we can remove unnecessary columns and get the excepted result.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDsAgCATAv3D2UEWgvsXw/2/UWJrgpgcOTjaszEntqkqF6homLxssoH9wI8h6tD0BGiA5wZg4wAI4t3BuGX+JnmEEHB8TrBWsFbxWsUVxqeWEBvS8413s/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Employee ID" = _t, #"Eval Score" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Employee ID", Int64.Type}, {"Eval Score", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Year]+1), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Year", "Employee ID"}, #"Added Custom", {"Custom", "Employee ID"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Eval Score"}, {"Added Custom.Eval Score"}), #"Sorted Rows" = Table.Sort(#"Expanded Added Custom",{{"Employee ID", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom.Eval Score", "Custom"}) in #"Removed Columns"Alternatively, We can achieve that by dax.
Column = VAR minyear = CALCULATE ( MIN ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Employee ID] ) ) RETURN IF ( 'Table'[Year] = minyear, "N", "Y" )Pbix as attached.
3 Replies
Just create a new column Previous year as: year - 1.
Then do a self join (merge tables in the UI) with year and previous year together and employee Id as the second join column (hold control to select multiple).
Expand the joined column data as needed.
Hi Anonymous ,
I have created a sample for your reference, please check the following steps as below.
1. add a custom column as below.
= Table.AddColumn(#"Changed Type", "Custom", each [Year]+1)2. Self merge the table like that.
3. Expand the Eval Score column and add a custom column.
= Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y")4. Then we can remove unnecessary columns and get the excepted result.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDsAgCATAv3D2UEWgvsXw/2/UWJrgpgcOTjaszEntqkqF6homLxssoH9wI8h6tD0BGiA5wZg4wAI4t3BuGX+JnmEEHB8TrBWsFbxWsUVxqeWEBvS8413s/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Employee ID" = _t, #"Eval Score" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Employee ID", Int64.Type}, {"Eval Score", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Year]+1), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Year", "Employee ID"}, #"Added Custom", {"Custom", "Employee ID"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Eval Score"}, {"Added Custom.Eval Score"}), #"Sorted Rows" = Table.Sort(#"Expanded Added Custom",{{"Employee ID", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom.Eval Score", "Custom"}) in #"Removed Columns"Alternatively, We can achieve that by dax.
Column = VAR minyear = CALCULATE ( MIN ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Employee ID] ) ) RETURN IF ( 'Table'[Year] = minyear, "N", "Y" )Pbix as attached.
Hello Anonymous
you can use this DAX-measure to achieve this. This measure checkes if the year is filtered. If its filtered it changes the filter to the year of the row + previous year. However... it does not check there are both years maintained.. .so simple consider that always the current year and the previous one is put as filter. Would that do it?
Average Year and previous year = var yearint =SELECTEDVALUE( YourTable[Year]) return IF( ISFILTERED(YourTable[Year]); CALCULATE( AVERAGE(YourTable[Valuation]); filter( ALL(YourTable[Year]); or(YourTable[Year]=yearint;YourTable[Year]=yearint-1) ) ); 0 )If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
