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
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
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'?
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?
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
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |