Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rosscortb
Post Patron
Post Patron

Calculating Rate of Increase

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
4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

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
Not applicable

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.

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
Solution Sage
Solution Sage

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.