cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ericlday
Frequent Visitor

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Check for previous year entry

Hi @ericlday ,

 

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.

Capture.PNG

 

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.

2.PNG

 

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

3.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Microsoft artemus
Microsoft

Re: Check for previous year entry

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.

Community Support
Community Support

Re: Check for previous year entry

Hi @ericlday ,

 

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.

Capture.PNG

 

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.

2.PNG

 

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

3.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Super User I
Super User I

Re: Check for previous year entry

Hello @ericlday 

 

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors