Calculating Rate of Increase

[rosscortb]

Hello 

I'm trying to calculate rate of increase between employees an employees salary increase. I worked it in excel as the below.  

a= Current Month Salary - Previous Month Salary

then

a / Previous month salary *100

I am struggling with the subtraction and previous month.

This doesnt work but given you the columns names

Salary = CALCULATE(SUM('DB Headcount'[Annual Salary]-CALCULATE(SUM('DB Headcount'[Annual Salary],-1))))

Need anything else please ask.

Thanks
Ross

[Cmcmahan]

First, it seems that the measure you shared with us doesn't work as you shared it. It seems to be missing parenthesis or parameters, or something.  Plus it seems weird to sum salaries.  Depending on your table structure, wouldn't that count the same person's salary multiple times?

There are of course, multiple ways to do this, using various time intelligence functions like DATESINPERIOD, PREVIOUSMONTH, DATEADD, DATESBETWEEN, or TOTALMTD, and which one is best depends on how your data is structured.  Could you share a sample in a copy/pastable format so that we can see how you have the data stored?

Also, this becomes a very difficult measure if you don't have a date dimension.  Do you have some sort of calendar table in your data model that is related to the date field in 'DB Headcount'?

[Anonymous]

I have created a date table with a relation to the fact table with salaries which I also mark as the date table. In this date table I have created a calculated column as below:

IsReportingDate = 
    IF( 
        'Date'[DateKey] = MAX( FactSalary[DateKey]);
        1;
        0
    )

The measure is then: 

Salary change = 
VAR __currentSalary = SUM( FactSalary[AnnualSalary])
VAR __priorSalary = CALCULATE( SUM( FactSalary[AnnualSalary]); PREVIOUSMONTH( 'Date'[Date]))

RETURN
DIVIDE(
    __currentSalary - __priorSalary + 0;
    __currentSalary;
    0
)

And if you filter the graph with the [IsReportingDate] or uses the date table in another way to filter the graph/table then you should be able to get the %-change.

[Cmcmahan]

Looks like @AnthonyTilley beat me to the punch.

So does the [Salary Change] measure you shared work for your purposes?  If not, what unwanted behavior are you seeing?

[AnthonyTilley]

are you able to provide a data sample 

if we brake down your formula you can see why its not working

Salary =

--calculate the sum of annual salary

CALCULATE(

SUM('DB Headcount'[Annual Salary]-

then take away the sum of annual salary 

CALCULATE(

SUM('DB Headcount'[Annual Salary],

and then -1

-1))))

at no point in the equation do you define how to diferentiate between the current and previous salary

here is an example i have 

growth = 
--get current date 
var d = 'Table'[date]
--find the previous salary by finding the largest date prior to this one 
var d2 = CALCULATE(max('Table'[date]),all('Table'),'Table'[date]<d)
--get the salary 
var s = CALCULATE(sum('Table'[Salary]),all('Table'),'Table'[date] = d2)
-- get A (Current Month Salary - Previous Month Salary)
var a = 'Table'[Salary]-s
-- finally devide by previous salary to get growth
var ret = DIVIDE(a,s)*100
return ret

i was using date to determin the previous month, however you differentiate in your table adapt the code above to reflect this