Salary depending on year

[Patop]

Hello,

I have 4 columns in a table corresponding to the salary of 2018, the salary of 2019 until 202 depending on differents persons.

I also have a table that contains dates from 2018 to 2021.

I would like to create a mesure that will sum and show the salary depending on my column dates.

Like if the date is 2021, I sum the columns salary of 2021, if the date is 2018, I do the same but with the column 2018

Hence i would like somethink like this

Thank you very much

Have a good day

[PC2790]

Have a DateTable with which you can create relationship with your Main Table.

Create a measure called sum of Salary like this 

Salary Sum = Sum(MainTable[Salary]) and then visualise it in your report against Year from DatesTable like below:

[Patop]

Hello,

Thanks for your answer but I have 4columns of salary so I just can't sum them i think, for exemple I have

and I would like to use my second table where I have dates to be able to have this:

Where the right columns is the sum of salary corresponding to the year

[PC2790]

Ok..Now that makes the problem much clearer.

You need to unpivot the columns here.

Go to Power Query and go to Advanced Editor and paste this code there after removing the exisiting code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7BDcAwCAN34Z0HCU6XQey/RklRCryswwajSpMGTWZ2WSGVbCittNCCuAlxkJjJDil0EnBoToHj7+xF++LveHIX7dJHZi8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Salary 2018" = _t, #"Salary 2019" = _t, #"Salary 2020" = _t, #"Salary 2021" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Salary 2018", Int64.Type}, {"Salary 2019", Int64.Type}, {"Salary 2020", Int64.Type}, {"Salary 2021", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.2", "Year"}, {"Value", "Salary"}})
in
    #"Renamed Columns"

The table would look like this:

You can now visualise it in the form of a table as below:

[Patop]

Thank you so much!

and in case I have this :

and I want to have this :

What should I change in the code in order to have this result?

I tried but I didn't succeed

Thank you!

[PC2790 · Accepted solution]

Here you go:

Steps to be followed on high level:

1. Unpivot all columns except "ID"
2. Split the new "Attribute" column using the " " delimiter. It will create two columns -Attribute and Year
3. Re-pivot the "Attribute" column.

M code as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dU9BDsAgCPsLZw846j5D/P83hsPEdM4DaRtKU9ylSpGqqgFXwkcNGmMxkF78VXMJsuJ0MZjl0loCqf0CwchJYve31Rv0xbHTveJA4fjt1B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Salary 2018" = _t, #"Salary 2019" = _t, #"Salary 2020" = _t, #"Salary 2021" = _t, #"Income 2018" = _t, #"Income 2019" = _t, #"Income 2020" = _t, #"Income 2021" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute", "Year"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Table looks like:

 And simply visualise it in the form of a table:

I hope this works for you.

Please provide a Kudos to this answer if you found it interesting.

If this post helps, then please consider accepting it as the solution to help other members find it more quickly

[Patop]

Thank you that's perfect