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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.