Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I am looking for a solution for my employees' salary analyses report.
I got stuck visualizing the date of the last change (increase) of the following dataset:
What I would like to achieve is the following table:
Thank's in advance!
Solved! Go to Solution.
Hi @Birdjo
One way could be a new calculated table
From the Modelling Tab >>>New Table
(Assuming your Table Name is Table1)
New Table = VAR temp = ADDCOLUMNS ( ADDCOLUMNS ( Table1, "PreviousSalary", VAR Previous_Date = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] < EARLIER ( Table1[Date] ) ) ) RETURN CALCULATE ( SUM ( Table1[Salary] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = Previous_Date ) ) ), "% increase", Table1[Salary] / [PreviousSalary] - 1 ) VAR temp2 = FILTER ( temp, NOT [% increase] IN { 0, 1 / 0 } ) VAR temp3 = ADDCOLUMNS ( temp2, "Maxdate", CALCULATE ( MAX ( Table1[Date] ), FILTER ( temp2, Table1[Employee] = EARLIER ( Table1[Employee] ) ) ) ) VAR Final_Table = FILTER ( temp3, [Date] = [Maxdate] ) RETURN Final_Table
Hi,
You requirement can be achieved through dynamic Measures... which you can modify in a later stage..
I am also addition another column for you ease .. to seee "Previous Salary"
I believe the change % should be wrt to last salary (not wrt latest salary )
Solution pics are mentioend belwo.... Hope you enjoyed it...
Please tick as ans if the solution BAMMED ur prob 😄
I have incorporated all VAR in on measure for your ease
Try this revision
See the attached file as well
New Table = VAR temp = ADDCOLUMNS ( ADDCOLUMNS ( Table1, "PreviousSalary", VAR Previous_Date = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Event] = "Salary" ) ) RETURN IF ( Table1[Event] = "Salary", CALCULATE ( SUM ( Table1[Salary] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = Previous_Date && Table1[Event] = "Salary" ) ) ) ), "% increase", Table1[Salary] / [PreviousSalary] - 1 ) VAR temp2 = FILTER ( temp, NOT [% increase] IN { 0, 1 / 0 } ) VAR temp3 = ADDCOLUMNS ( temp2, "Maxdate", CALCULATE ( MAX ( Table1[Date] ), FILTER ( temp2, Table1[Employee] = EARLIER ( Table1[Employee] ) && Table1[Event] = "Salary" ) ) ) RETURN FILTER ( temp3, [Date] = [Maxdate] )
I'm sorry I didn't made it clear. The [Event] column is something I write manually just to demonstrate how changes could be something different than a raise. I don't have [Event] column in the original dataset.
Hi @Birdjo
This gets really tough then
What if we add a calculated column to get the "Event" column
I mean a salary would exist at least 2 or 3 times for an employee...while bonus and raise might be unique figures for one employee
So a calculated column like
Event = VAR myCount = CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Employee], Table1[Salary] ) ) RETURN IF ( myCount > 1, "Salary", "Others" )
It is really tough indeed. 😄
That's clever but what if the same bonus occurs two times or the raise was in the last month?
It is easy for a human to decide whether it is a raise or something else, however recreating this thought process with DAX seems hard to me.
I have some progress with this case.
I have the employee salaries as in their contract. So bonuses are not included there. Only raises and very slight increases and decreases like -2% to +2%.
And I decided to use @Zubair_Muhammad's solution with the calculated table as the other one doesn't behave well because it looks for MAX(Table1[Salary])
Do you have any ideas how to implement some treshold in it?
Hi,Let me try with this:
Add a Calculated Column in your Salary Table:
Assuming you have Every Month Salary of the Employees.
Variation = DIVIDE ( Salary[Salary] - CALCULATE ( MAX ( Salary[Salary] ); FILTER ( Salary; Salary[Employee] = EARLIER ( Salary[Employee] ) ); DATEADD ( Salary[Date]; -1; MONTH ) ); CALCULATE ( MAX ( Salary[Salary] ); FILTER ( Salary; Salary[Employee] = EARLIER ( Salary[Employee] ) ); DATEADD ( Salary[Date]; -1; MONTH ) ) )
Now, create the measures:
LastChange = CALCULATE(Max(Salary[Date]);FILTER(Salary;Salary[Variation]<>0 ))
MSalary = VAR LCHANGE = [LastChange] RETURN CALCULATE ( MAX ( Salary[Salary] ); FILTER ( Salary; Salary[Date] = LCHANGE ) )
M%&Var = VAR LCHANGE = [LastChange] RETURN CALCULATE ( MAX ( Salary[Variation] ); FILTER ( Salary; Salary[Date] = LCHANGE ) )
Regards
Victor
Thanks everybody!
I tested the calculated table and it works fine. However seems like my data isn't that clean and there are some changes in the salaries which are not raises. There 1 month increases such as bonuses and some 1 month decreases in some cases.
I like the calculated column solution although I didn't try it.
Here is more realistic look of my data. The last column called "Event" is something that I wrote manually.
In this case the formulas return the last change date as 01.10.2017, however a person can identify that the last raise was on 01.08.2017.
It seems like this case is a serious challenge.
Do you have any ideas how to resolve it?
Hi @Birdjo
One way could be a new calculated table
From the Modelling Tab >>>New Table
(Assuming your Table Name is Table1)
New Table = VAR temp = ADDCOLUMNS ( ADDCOLUMNS ( Table1, "PreviousSalary", VAR Previous_Date = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] < EARLIER ( Table1[Date] ) ) ) RETURN CALCULATE ( SUM ( Table1[Salary] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = Previous_Date ) ) ), "% increase", Table1[Salary] / [PreviousSalary] - 1 ) VAR temp2 = FILTER ( temp, NOT [% increase] IN { 0, 1 / 0 } ) VAR temp3 = ADDCOLUMNS ( temp2, "Maxdate", CALCULATE ( MAX ( Table1[Date] ), FILTER ( temp2, Table1[Employee] = EARLIER ( Table1[Employee] ) ) ) ) VAR Final_Table = FILTER ( temp3, [Date] = [Maxdate] ) RETURN Final_Table
Please see attached file
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |