Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Birdjo
Resolver II
Resolver II

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
Zubair_Muhammad
Community Champion
Community Champion

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


 


Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
afzalphatan
Resolver I
Resolver I

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

 

1.PNG5.PNG4.PNG3.PNG2.PNG

 

 

@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] )

Regards
Zubair

Please try my custom visuals

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


Regards
Zubair

Please try my custom visuals

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?

 

 

Vvelarde
Community Champion
Community Champion

@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

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?

Zubair_Muhammad
Community Champion
Community Champion

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


 


Regards
Zubair

Please try my custom visuals

@Birdjo

 

Please see attached file

 

Dateoflastchange.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors