Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Thank's in advance!
Solved! Go to Solution.
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |