how to calculate salary changes

[selected_]

I'm trying to calculate salary growth percentages in Power BI and need some help.

I have a table called "compensationHistories" that contains salary history data for employees. The key columns are:

• Business Unit in a different table name "BU"
• Employee ID
• Date fields like lastModifiedOn, start date, end date
• PayCompValue - which contains the employee's monthly salary, employee can have multiple records depending on salary increase or decrease.
• EventReasonDescription - which has the reason for salary changes like "Annual wage revision"

What I want to calculate is the year-over-year salary percentage change for each employee at the company level which is Business Unit.

In other words, for each company (Business Unit), I need to take the total amount given as "Annual wage revision" and divide it by the total salaries (sum of PayCompValue) to get the average % salary increase/decrease for that company in that year.

Can anyone provide suggestions on the DAX formula I would need to calculate this percentage change based on the data and structure I've described? I'm having trouble figuring out how to group and summarize the data correctly in Power BI.

[DataNinja777 · Accepted solution]

Hi @selected_ ,

You can produce your required output in multiple different ways, and one of them is to use a disconnected calendar table and dax to calculate the salary for the period between the start and end dates, and the sumxing it over the calendar table and employee dimension table.

First I created a fake data using your required column fields which shows salary changes over years for multiple employees.  

Then, in order to visualize the salary over time periods, I've created the visualization like below using concatenatex as an intermediary step.  

The dax measure used for the above visualization is as follows:

Salary by month = 
VAR result =
    CONCATENATEX (
        DISTINCT ( 'Salary table' ),
        IF (
            'Salary table'[Start Date] <= MAX ( 'Calendar'[Date] )
                && 'Salary table'[End Date] >= MAX ( 'Calendar'[Date] )
                || 'Salary table'[Start Date] <= MIN ( 'Calendar'[Date] )
                    && 'Salary table'[End Date] >= MIN ( 'Calendar'[Date] ),
            'Salary table'[Monthly Salary] & " ,",
            BLANK ()
        )
    )
RETURN
    LEFT ( result, LEN ( result ) - 1 )
        & IF ( [Changed date] = BLANK (), BLANK (), "(from " & [Changed date] & ")" )

I am assuming that the salary is fixed on a monthly basis and not prorated for the countrows of the days in months which varies betweeen 28 days (for February) and 31 days (for January, March, May, July, August, October, December).  

Nevertheless, I prorated the salary for the old and new salary in the month the salaries are revised. Although companies tend to revise salary on the 1st of the month once a year, the dummy data used in this example uses varied salary modification dates throughout the months, and timing of the salary revision months are also different for each employee to demonstrate the fact that dax and Power BI data model is able to handle these kinds of irregularities.

In order to visualize each step, I created 4 different measures for proration calculation as follows:

First, calculate the share of the days in the month after the salary change:

After change =
VAR result_plus_1 =
    MAXX (
        DISTINCT ( 'Salary table' ),
        IF (
            'Salary table'[Start Date] >= MIN ( 'Calendar'[Date] )
                && 'Salary table'[Start Date] <= MAX ( 'Calendar'[Date] ),
            DAY ( 'Salary table'[Start Date] ),
            BLANK ()
        )
    )
VAR result =
    IF ( result_plus_1 = BLANK (), BLANK (), result_plus_1 - 1 )
VAR Number_Of_Days =
    COUNTROWS ( 'Calendar' )
VAR Before_Change =
    DIVIDE ( result, VALUE ( IF ( result = BLANK (), BLANK (), Number_Of_Days ) ) )
RETURN
    IF (
        result_plus_1 = 1,
        1,
        IF ( Before_Change = BLANK (), BLANK (), 1 - Before_Change )
    )

Then calculate the share of the days in the month before the salary change to make up the 100% total together with the measure above.  

Before change = if([After change]=blank(),blank(),1-[After change])

The above two measures are the % of the countrows of the calendar month number of dates, which represent before the salary change and after the salary change in the prorated month.  

Then next, additional two measures to represent the monthly salary before the change and after the change are calculated in the following manner. 

Before change value = 
VAR result =
    MAXX (
        DISTINCT ( 'Salary table' ),
        IF (
            'Salary table'[Start Date] < MIN ( 'Calendar'[Date] ),
            'Salary table'[Monthly Salary]
        )
    )
RETURN
    IF ( [Changed date] = BLANK (), BLANK (), result )

After change value =
VAR result =
    MAXX (
        DISTINCT ( 'Salary table' ),
        IF (
            'Salary table'[Start Date] <= MAX ( 'Calendar'[Date] ),
            'Salary table'[Monthly Salary]
        )
    )
RETURN
    IF ( [Changed date] = BLANK (), BLANK (), result )

I've written those in separate measures instead of combining in one big formula with VAR for easier debugging while preparing. 

The proration measure is produced from the combination of the above 4 formulas:  

Prorated for changed month = round([Before change]*[Before change value]+[After change]*[After change value],0)

The resultant intermediary visualization is as shown below.

Then I've written the measure below for the final output analysis.  

Salary by month value =
VAR result =
    MAXX (
        'Salary table',
        IF (
            'Salary table'[Start Date] <= MAX ( 'Calendar'[Date] )
                && 'Salary table'[End Date] >= MAX ( 'Calendar'[Date] )
                || 'Salary table'[Start Date] <= MIN ( 'Calendar'[Date] )
                    && 'Salary table'[End Date] >= MIN ( 'Calendar'[Date] ),
            'Salary table'[Monthly Salary]
        )
    )
RETURN
    SUMX (
        VALUES ( 'Calendar'[yyyy-mm] ),
        IF ( [Changed date] = BLANK (), result, [Prorated for changed month] )
    )

In order to deal with the common issue of subtotals not producing the correct subtotaling problem, I've wrapped the above measure in another sumxing measure over dimension tables like below:

Salary by month value sumxing over employee & BU =
SUMX ( BU, SUMX ( Employee, [Salary by month value] ) )

In order to make the month with the salary change stand out, I've highlighted those month in pink using the following color dax formula.  

Change month Color =
VAR result =
    MAXX (
        'Salary table',
        IF (
            'Salary table'[Start Date] <= MAX ( 'Calendar'[Date] )
                && 'Salary table'[End Date] >= MAX ( 'Calendar'[Date] )
                || 'Salary table'[Start Date] <= MIN ( 'Calendar'[Date] )
                    && 'Salary table'[End Date] >= MIN ( 'Calendar'[Date] ),
            'Salary table'[Monthly Salary]
        )
    )
RETURN
    IF ( [Changed date] <> BLANK (), "Pink" )

Then used that color measure in the conditional formatting of the output measure which sumxes over employee and BU.  This produces visualization like below, which highlights salary change month for easier visual identification.  

For the final required output, I've used the following measures for the current year salary:

Selected year salary =
CALCULATE (
    [Salary by month value sumxing over employee & BU],
    FILTER ( 'Calendar', 'Calendar'[yyyy] = MAX ( 'Calendar'[yyyy] ) )
)

Then for previous year salary the measure below is used:

Selected previous year = VAR Previous=max('Calendar'[yyyy])-1
 return
 calculate([Salary by month value sumxing over employee & BU],'Calendar'[yyyy]=Previous)

Finally, for year over year salary change which respects your year slicer selection:

Year over year change = divide([Selected year salary],[Selected previous year])

The resultant final output is as shown below.  

Last but not the least, the the data model used for this excecise looks like below:

As mentioned before, the calendar table is kept as a disconnected table to enable flexible duration analysis while separate employee and BU dimension tables were created using summarize table function, as there are multiple employees in the salary fact table, we needed to link the fact table with the employee dimension table in order to

sumx over the dimension table to get the correct summation over the employee ID.  

I attach an example pbix file.  

Best regards