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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.