DAX - Average (measure) based on most recent Date

[snph1777]

My Power BI model has a single table (only).

I need to develop 2 measures, one for Salary of an employee based on most recent effective date; the other for Average Salary of an employee, based on all such instances of most recent effetive dates.

For each employee, get the most recent effective date, and the corresponding salary on the most recent effective date; then develop 2 measures, one for the Sum of Salaries, and the other for the Average of Salaries.

James: the most recent date is Aug 25, 2025; the salary is 5,000.00.

John: the most recent date is Aug 15, 2025; the salary is 38,000.00.

Mike: the most recent date is Jun 30, 2025; the salary is 18,000.00.

TotalSalary_Measure = 61,000.00

AverageSalary_Measure = 20,333.33

I need the Salary to be filtered only by the employee's most recent effetive date; for example, in the case of John, even if I have a slicer for Department that reads Engineering, I need the salary as 38,000, NOT, 12,000.

In the case of James, even if I have a slicer for Job that reads Analyst, I need the salary as 5,000 NOT, 9,000.

See the files attached for more details.

You can see the .pbix file here.

[v-echaithra]

Hi @snph1777 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[snph1777]

@v-echaithra     all the answers here are good; it depends on one's need, and desire to follow the best practices.

Say, you may now want a calculated column; or you cannot create a calculated table due to client restriction; context transition needs to be avoided to improve performance; avoiding CALCULATE function inside SUMMARIZE, these factors matter.

I generally avoid ALLEXCEPT too, although am not against it.

Thanks to all of you. Appreciate your time.

[johnt75 · Accepted solution]

You can create a couple of measures like

Total Salary =
VAR EmpWithAllSalaries = CALCULATETABLE(
    SUMMARIZE(
        Table1,
        Table1[EmployeeName],
        Table1[EffectiveDate],
        Table1[Salary]
    ),
    ALLEXCEPT( Table1,
        Table1[EmployeeName],
        Table1[EffectiveDate],
        Table1[Salary]
    )
)
VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) )
VAR Result = SUMX( EmpWithLatestSalary, Table1[Salary] )
RETURN Result

Average Salary =
VAR EmpWithAllSalaries = CALCULATETABLE(
    SUMMARIZE(
        Table1,
        Table1[EmployeeName],
        Table1[EffectiveDate],
        Table1[Salary]
    ),
    ALLEXCEPT( Table1,
        Table1[EmployeeName],
        Table1[EffectiveDate],
        Table1[Salary]
    )
)
VAR EmpWithLatestSalary = INDEX( 1, EmpWithAllSalaries, ORDERBY( Table1[EffectiveDate], DESC ), PARTITIONBY( Table1[EmployeeName] ) )
VAR Result = AVERAGEX( EmpWithLatestSalary, Table1[Salary] )
RETURN Result

[snph1777]

@johnt75   thanks, i will get back; it look good.

[v-echaithra]

Hi @snph1777 ,

Thank you @Shahid12523 , @Ashish_Mathur , @ryan_mayu  for the response.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

[snph1777]

@v-echaithra  @Shahid12523 , @Ashish_Mathur , @ryan_mayu

Thanks everyone; appreciate your time.

I am trying to get a solution that avoids a alculated column or calculated table.

Also, it is not a good practice to use CALCULATE function inside SUMMARIZE function, even if the numbers are right. However, I will evalated the options and get back.

[Shahid12523]

1. Salary on Most Recent Effective Date (per employee)
LatestSalary =
VAR LatestDate =
CALCULATE(
MAX('EmployeeData'[Effective Date]),
ALLEXCEPT('EmployeeData', 'EmployeeData'[Employee Name])
)
RETURN
CALCULATE(
MAX('EmployeeData'[Salary]),
'EmployeeData'[Effective Date] = LatestDate,
ALLEXCEPT('EmployeeData', 'EmployeeData'[Employee Name])
)

This gives you the correct salary per employee, even if slicers are applied.

2. Total Salary (across all employees on their latest date)
TotalLatestSalary =
SUMX(
ADDCOLUMNS(
SUMMARIZE('EmployeeData', 'EmployeeData'[Employee Name]),
"LatestDate", CALCULATE(MAX('EmployeeData'[Effective Date])),
"LatestSalary",
CALCULATE(
MAX('EmployeeData'[Salary]),
'EmployeeData'[Effective Date] = CALCULATE(MAX('EmployeeData'[Effective Date]))
)
),
[LatestSalary]
)

3. Average Salary (based on most recent date per employee)
AverageLatestSalary =
AVERAGEX(
ADDCOLUMNS(
SUMMARIZE('EmployeeData', 'EmployeeData'[Employee Name]),
"LatestDate", CALCULATE(MAX('EmployeeData'[Effective Date])),
"LatestSalary",
CALCULATE(
MAX('EmployeeData'[Salary]),
'EmployeeData'[Effective Date] = CALCULATE(MAX('EmployeeData'[Effective Date]))
)
),
[LatestSalary]
)

[snph1777]

@Shahid12523   Thanks, I will get back

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[snph1777]

@Ashish_Mathur   Thanks. I will get back

[snph1777]

@Ashish_Mathur   I will not be able to add any new Calculated Table into the model; so cannot use the Date table you have created.

[ryan_mayu]

@snph1777 

you can create a column

Column = if('Table'[EffectiveDate]=CALCULATE(max('Table'[EffectiveDate]),ALLEXCEPT('Table','Table'[EmployeeName])),"y")

 

then create two measures

Measure = CALCULATE(sum('Table'[Salary]),FILTER('Table','Table'[Column]="y"))

Measure 2 = [Measure]/DISTINCTCOUNT('Table'[EmployeeName])

pls see the attachment below

[snph1777]

@ryan_mayu

Thanks. But can you provide a solution that avoids the creation of a calculated column? The table has too many rows, nearly a million; a context transisiton in the column can be expensive.

[ryan_mayu]

@snph1777 

you can try this measure to avoid to create a calculated column

SumLatestSalaries =

SUMX(

    SUMMARIZE(

        'Table',

        'Table'[EmployeeName],

        "LatestSalary",

        CALCULATE(

            MAXX(

                FILTER(

                    'Table',

                    'Table'[EffectiveDate] = CALCULATE(MAX('Table'[EffectiveDate]), ALLEXCEPT('Table', 'Table'[EmployeeName]))

                ),

                'Table'[Salary]

            )

        )

    ),

    [LatestSalary]

)