cancel
Showing results for
Did you mean:
Birdjo Member

Date of last change

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: 1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Date of last change

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 =
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 =
temp2,
"Maxdate", CALCULATE (
MAX ( Table1[Date] ),
FILTER ( temp2, Table1[Employee] = EARLIER ( Table1[Employee] ) )
)
)
VAR Final_Table =
FILTER ( temp3, [Date] = [Maxdate] )
RETURN
Final_Table

10 REPLIES 10 Super User

Re: Date of last change

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 =
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 =
temp2,
"Maxdate", CALCULATE (
MAX ( Table1[Date] ),
FILTER ( temp2, Table1[Employee] = EARLIER ( Table1[Employee] ) )
)
)
VAR Final_Table =
FILTER ( temp3, [Date] = [Maxdate] )
RETURN
Final_Table

Highlighted Super User

Re: Date of last change

@Birdjo

Please see attached file Vvelarde Super Contributor

Re: Date of last change

@Birdjo

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

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

Proud to be a Datanaut!

afzalphatan Member

Re: Date of last change

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     Birdjo Member

Re: Date of last change

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? Super User

Re: Date of last change

@Birdjo

Try this revision

See the attached file as well

New Table =
VAR temp =
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 =
temp2,
"Maxdate", CALCULATE (
MAX ( Table1[Date] ),
FILTER (
temp2,
Table1[Employee] = EARLIER ( Table1[Employee] )
&& Table1[Event] = "Salary"
)
)
)
RETURN
FILTER ( temp3, [Date] = [Maxdate] )
Birdjo Member

Re: Date of last change

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. Super User

Re: Date of last change

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" ) Birdjo Member

Re: Date of last change

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.

Announcements Top Kudoed Authors
Users Online
Currently online: 114 members 1,617 guests
Recent signins:
• hellmannnnnn • dm-p • BusinessIntel • conniedevina • • JCPO • zilaifeng • Bino • kyliecoast • aditya_kamdi • v-ankojh • smoncayo • jkmann • DBS 