cancel
Showing results for
Did you mean:
Highlighted
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```

Super User

## Re: Date of last change

@Birdjo

Super Contributor

## Re: Date of last change

@Birdjo

Hi,Let me try with this:

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

Proud to be a Datanaut!

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

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] )```
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" )```

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

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 172 members 1,741 guests
Recent signins: