cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Birdjo Member
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:

dataset.png

 

What I would like to achieve is the following table:
result.png

 

Thank's in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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
Super User

Re: Date of last change

@Birdjo

 

Please see attached file

 

Dateoflastchange.png

Vvelarde Super Contributor
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 )
    )

Img1.png

 

Regards

 

Victor

 

 




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

Proud to be a Datanaut!




afzalphatan Member
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 Smiley Very Happy

 

I have incorporated all VAR in on measure for your ease

 

1.PNG5.PNG4.PNG3.PNG2.PNG

 

 

Highlighted
Birdjo Member
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.
salaries.png

 

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

Re: Date of last change

Hi @Birdjo

 

This gets really tough then Smiley Tongue

 

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

events.png

Birdjo Member
Member

Re: Date of last change

It is really tough indeed. Smiley Very Happy

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 41 members 942 guests
Please welcome our newest community members: