Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have already calculated the salary expenses of my employees over time, with a formula that I show below. Now I want to calculate a growth rate of the salary expenses, over the years and the months.
I use the formula below to create a measure and know the salary expenses over time.
CurrentSalary sum=
VAR currentDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'salary'[Amount] );
FILTER ( 'Salary'; 'Salary'[Start] <= currentDate && 'Salary'[End] >= currentDate )
) )
In my database, each employee has several lines with the registration of the various salaries they have received over time.
Employee | Start | End | Salary |
1 | 01/01/2005 | 31/12/2005 | 1 900,00 € |
1 | 01/01/2006 | 31/12/2006 | 1 950,00 € |
1 | 01/01/2007 | 31/12/2007 | 2 000,00 € |
1 | 01/01/2008 | 31/12/2015 | 2 050,00 € |
1 | 01/01/2016 | 31/12/9999 | 2 100,00 € |
2 | 01/01/2014 | 31/12/2015 | 5 250,00 € |
2 | 01/02/2010 | 31/12/2013 | 2 000,00 € |
2 | 01/01/2016 | 31/12/9999 | 5 250,00 € |
3 | 01/10/2008 | 31/12/2009 | 950,00 € |
3 | 01/01/2010 | 31/03/2010 | 1 000,00 € |
3 | 01/04/2010 | 30/09/2010 | 1 200,00 € |
3 | 01/10/2010 | 31/07/2011 | 1 390,00 € |
3 | 01/03/2014 | 30/04/2015 | 2 680,00 € |
3 | 01/08/2011 | 28/02/2014 | 2 600,00 € |
3 | 01/05/2015 | 31/12/2015 | 4 000,00 € |
3 | 01/01/2016 | 31/12/9999 | 4 500,00 € |
Solved! Go to Solution.
In your measure you try to sum up the column [Montante]. But for the totals you need to sum up the column [Salary Atual soma]
Hi aempa,
as long your salary always become bigger you can create a calculated column like this.
Groth = var actual = CALCULATE(MAX(Tabelle2[Salary]);Tabelle2[Employee]=EARLIER(Tabelle2[Employee])) var last = CALCULATE(MAX(Tabelle2[Salary]);FILTER(ALL(Tabelle2);Tabelle2[Start]<EARLIER(Tabelle2[Start]));Tabelle2[Employee]=EARLIER(Tabelle2[Employee])) return FORMAT(DIVIDE(actual;last;1);"percent")
Thank you @spuder, but I don't understand in line 1 and 2, the difference between the two values aren't 102%. Am I reading the data correctly?
1950 is 102,63% of 1900 (1950*100/1900)
If you wish just the groth you have to divide by 100%
so the result would be 2,63%.
Or am I wrong with your needs?
Sorry, I am a little rookie on this.
I want to know the growth between January and February or 2012 and 2013.
In this moment, I have this results:
I can't link the date table because my "Salary atual soma" are influence negatively.
In this scenario I would choose the following approach.
Using PREVIOUSMONTH() and PREVIOUSYEAR()
In your measure you try to sum up the column [Montante]. But for the totals you need to sum up the column [Salary Atual soma]