Total on Row by Year by Employee from previous year value

[Samkar78]

Hi,

In a HR report I have to compare the current employees set up : how much they shoul be paid, in what service there are in.... and how much they have been paid last year.
The problem is : employees can have a different Salary set up, a different contrat, a different service...etc from day to day.

In my pay slip table I have all the informations but I cant't find the right measure.

I tried to combine a ALLEXCEPT function to calculate the sum on all lines per employee  and per year with a PREVIOUSYEAR function but it dosent'work

Here is a simplified sample of the Pay slip table :

Employee ID;Contract Number;Service; Gross Month. Salary ; Gross Anual Salary ;Year;Date; Net paid ; Bonus paid
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/09/2023; 2 208,32 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/08/2023; 2 208,87 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/07/2023; 2 448,13 ; 300,00
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/06/2023; 2 208,86 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/05/2023; 2 208,32 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/04/2023; 2 208,33 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/03/2023; 2 850,77 ; 300,00
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/02/2023; 2 245,16 ;
889079;2;Communication; 2 750,00 ; 33 000,00 ;2023;01/01/2023; 2 006,45 ;
889079;2;Communication; 2 500,00 ; 30 000,00 ;2022;01/12/2022; 2 087,18 ;
889079;2;Communication; 2 500,00 ; 30 000,00 ;2022;01/11/2022; 2 007,61 ;
889079;2;Communication; 2 500,00 ; 30 000,00 ;2022;01/10/2022; 2 246,33 ; 300,00
889079;2;Communication; 2 334,00 ; 28 008,00 ;2022;01/09/2022; 1 685,12 ;
889079;2;Communication; 2 500,00 ; 30 000,00 ;2022;01/09/2022; 201,86 ;
889079;2;Communication; 2 334,00 ; 28 008,00 ;2022;01/08/2022; 1 590,73 ; 300,00
889079;2;Communication; 2 334,00 ; 28 008,00 ;2022;01/07/2022; 177,37 ;
889079;1;HR; 2 334,00 ; 28 008,00 ;2022;01/07/2022; 2 918,75 ;
889079;1;HR; 2 334,00 ; 28 008,00 ;2022;01/06/2022; 1 908,23 ;
889079;1;HR; 2 334,00 ; 28 008,00 ;2022;01/05/2022; 2 123,35 ; 300,00
889079;1;HR; 2 334,00 ; 28 008,00 ;2022;01/04/2022; 1 884,64 ;
889079;1;HR; 2 334,00 ; 28 008,00 ;2022;01/03/2022; 1 685,10 ;

KO: 
Previous year total salary paid = 

var AmEmpYr =

CALCULATE (

    sum(PAY_SLIP[Net paid]),

    ALLEXCEPT(PAY_SLIP, DATE_TABLE[Year] , PAY_SLIP[Employee ID])

)

RETURN CALCULATE(AmEmpYr, PREVIOUSYEAR(DATE_TABLE[Date]))

[Samkar78]

The workaround solution is to filter the dimensions on the last value (in yellow in my screenshot) and to use a PREVIOUSYEAR function for the measures.

[Samkar78]

Thanks again Elvin, 
I'll try that

[_elbpower · Accepted solution]

Hi  Sam,

You can create a sum measure and then use a matrix to show the years.
Since you are already restrciting the data to come for last two years and have a ranking column, you can use that for filtering.

Please accept the answer if it solves your query.