Salary revision still showing for 2024 but none salary revision was actually made

[selected_]

i have couple column that show currentSalary and prevSalary in a table, i wanna create another column and see the % of how much is difference in increase or decrease or if it not any changes at the salary between these two columns, but take consideration to the dates since i have different columns with dates like startDate, endDate and lastmodifiedDate If anyone gets a salary revision, Startdate defines from when the revision is effective. EndDate is when the Current Salary change gets over and day after that another salary change. For current salary, enddate is 9999-12-31 Lastmodified date defines when the change was made.

 I tried to create a simple measure like dividing the DIVIDE(([Current] - [Prev] 

but that shows figures for year 2024 when employees didn't had any revision yet so i dont know where it gets those figures from, so i guess in my measure i didnt take considration to those date columns in the tbale maybe? 

[v-yohua-msft · Accepted solution]

Hi, @selected_ 

I create a sample table:

Maybe you can try the following dax, create measures:

Effective Start Date = MAX('Table'[Start Date])

Effective End Date = IF(MAX('Table'[End Date]) = DATE(9999, 12, 31), TODAY(), MAX('Table'[End Date]))

Salary Difference = SUM('Table'[Current Salary])-SUM('Table'[Previous Salary])

Filtered Salary Difference = 
VAR CurrentEmployee = SELECTEDVALUE('Table'[Employee ID])
RETURN
CALCULATE(
   'Table'[Salary Difference],
    FILTER(
        'Table',
        'Table'[Employee ID] = CurrentEmployee &&
        'Table'[Last Modified Date] = MAX('Table'[Last Modified Date]) &&
        'Table'[Start Date] <= [Effective End Date] &&
        'Table'[End Date] >= [Effective Start Date]
    )
)

Percentage Change = [Filtered Salary Difference]/SUM('Table'[Previous Salary])

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.