cancel
Showing results for
Search instead 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: Thank's in advance!

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 =
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```

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 =
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``` Super User

## Re: Date of last change

@Birdjo

Please see attached file Highlighted
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 =
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] )```
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.

## Helpful resources

Announcements
Top Ideas Top Kudoed Authors
Users Online
Currently online: 25 members 888 guests
Recent signins:
• sreeni • gseretse • Fuhrer786 • MCornish • Max64 • Stachu • THEG72 Please welcome our newest community members:
• ADR • CT_Ciu • kdowling • d474boy • Yousef_3374 • Khrabby • DimG 