Compare amounts last year to this year for entries that have changed category

[Anonymous]

I am working on a problem with employee wages, and their development over time. We have employees on different pay scale levels, and would like to know three things:

1. How does the wages develop year over year for each pay scale
2. How does the wages develop year over year for employees who are still on the same pay scale level
3. How does the wages develop year over year for employees who change level (divided into moving up and down)

For this I have all wage data for all employees. I can't post the raw data here for obvious reasons, but I have compiled a mock set below, and provided the formulas I have used and how far I have gotten. 

Data:

The data consists of a FACT table, with all of our wage data, and several DIM tables with dimensions like definitions of the pay scale levels, a table of dates etc. I'll try to summarize the relevant databases, and give a sample. 

FACT - a table of all wage entries per employee per year

DIM_JOB - a table with each position, and their level both as a text entry and a numerical value, of descending order according to pay scale level (highest paid is 1, lowest 9)

DIM_DATE_OLD - a table with dates, and the corresponding Financial Year and Month for each day in the calendar year

FACT:

Personnel Number	Job Key	End of IN period	Amount	DIM_JOB.Rank	DIM_JOB.Rank LastYear
1	1007	31.03.2021	25000	7
1	1007	30.04.2021	25000	7
1	1006	31.05.2022	30000	6	7
1	1006	30.06.2022	30000	6	7
2	1005	31.03.2021	33000	5
2	1005	30.04.2021	33000	5
2	1005	31.05.2022	34000	5	5
2	1005	30.06.2022	34000	5	5
3	1006	31.03.2021	31000	6
3	1006	30.04.2021	31000	6
3	1007	31.05.2022	28000	7	6
3	1007	30.06.2022	28000	7	6
4	1004	31.03.2021	45000	4
4	1004	30.04.2021	45000	4
4	1003	31.05.2022	50000	3	4
4	1003	30.06.2022	50000	3	4

Columns:

Personnel number - a unique number to each employee

Job Key - the key value to the employee's position. 

End of IN period - the date the pay was paid out

Amount - amount paid out

DIM_JOB.Rank - a merged column from the DIM_JOB database, where Job Key in FACT is matched with Job Key in DIM_JOB, and Rank from DIM_JOB is inserted into FACT.

DIM_JOB.Rank LastYear - a calculated column to find the DIM_JOB.Rank of the emolyee the previous year. Code:

=
LOOKUPVALUE (
    'FACT'[DIM_JOB.Rank];
    'FACT'[Personnel Number]; 'FACT'[Personnel Number];
    'FACT'[End of IN period]; SAMEPERIODLASTYEAR ( 'FACT'[End of IN period] )
)

DIM_JOB is a table with rows of unique job keys, several of which have the same Job Rank. 

DIM_DATE_OLD is a table with a row for each day, and columns with the corresponding Financial Year, Month, etc.

I have the following measures:

To form a base for base salary calculations (there are more amount types that this measure filters out, but it would just add complexity here)

Base Salary :=
CALCULATE ( [Amounts] )

To form a measure for the base salary per month, and introduce the time dimension

Base salary month :=
CALCULATE ( [Base Salary]; DATESMTD ( DIM_DATE_OLD[Dato] ) )

Based on the above, I have found the salary of employees who have gotten a promotion (moved to a job rank with a lower numerical value), using the following formula:

This year salary for promotions :=
CALCULATE (
[Base salary month];
FILTER ( 'FACT'; 'FACT'[DIM_JOB.Rank] < 'FACT'[DIM_JOB.Rank LastYear] )
)

Now I run into problems, trying to get the value for last year. I have the following measure, returning the total base salary last year:

Base salary last year :=
CALCULATE ( [Base salary month]; SAMEPERIODLASTYEAR ( DIM_DATE_OLD[Dato] ) )

Using this, I thougth I could do the same as "This year salary for promotions", like this:

Last year salary for promotions :=
CALCULATE (
[Base salary last year];
FILTER ( 'FACT'; 'FACT'[DIM_JOB.Rank] < 'FACT'[DIM_JOB.Rank LastYear] )
)

I also tried using a SUMX formula, like this:

Last year salary for promotions :=
SUMX (
FILTER ( 'FACT'; 'FACT'[DIM_JOB.Rank NextYear] < 'FACT'[DIM_JOB.Rank] );
[Base salary last year]
)

Both of the above return empty for all employees. 

I have also tried numerous other solutions, including looking up last year's value using a similar formula to DIM_JOB.Rank LastYear, but we have several different amounts (corrections, additions, bonus, vacation money etc.), so it doesn't return a single value, and as such it returns an error.

What I'm looking for (given the dummy data) is the following:

A code that classify Personnel Number 1 and 4 as promotions (Gone from a high rank last year to a lower rank last year), and return their salary this year, and their salary last year. I want them both per line (so I can look at it per personnel number), and as an aggregate, so I can look at it per Job Rank. For this I would like to be able to see for all employees who are now e.g. on rank 6, and were on a numerically higher rank last year (promotions) what their wage is now, and what their wage was last year. That way I can find the percentage increase.

Once I figure out how to do it for promotions, I'm sure I can extrapolate that solution and figure out the rest myself.

I hope I have provided enough relevant information, and hope it's possible to do what I am trying to do. 

Thanks in advance,

EspenST

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample. As you also want to get the comparison of the previous year and last year, I modify the sample to include data from the previous year.

Here for this year VS last year, Personnel Number 1 and 4 can get the result. For last year VS previous year, Personnel Number 2 can get the result. The result I got according to your formula is:

It does not distinguish between years and directly shows all the results, including last year's growth. If I create a year column in the DIM_DATE_OLD table and put the column in the visual.

It can be seen in which year the increase was made. I mean, is there no need to create Last year salary for promotions, just use this one measure This year salary for promotions to see the annual growth.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft 

Thank you for the answer. I believe you are rigth. I got some help from a colleague as well and we realized that the issue was in fact with the classifications on job rank over time, and not so much with the measure.

We wanted to find both the salary development for the person, and for the job rank. For the job rank, we wanted to compare what they had last year (at a different rank) to what they make today, and our result came out as below:

Our solution wasn't actually with remaking the measure, but to make a new calculated column that matched the salary last year with the job rank of current year, so we could get the data out on one line, like this. 

So now it works! 🙂 

Thanks for your help!