Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

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