DAX - Average (measure) based on most recent Date

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.

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

14 Replies

ryan_mayu
Super User
1 year ago
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

Average (measure) based on most recent Date.pbix45 KB
- snph1777
  Helper V
  1 year ago
  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
    Super User
    1 year ago
    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]
    
    )
Ashish_Mathur
Super User
1 year ago
Hi,

PBI file attached.

Hope this helps.

Average.pbix53 KB
- snph1777
  Helper V
  1 year ago
  Ashish_Mathur Thanks. I will get back
  - snph1777
    Helper V
    1 year ago
    @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.
Shahid12523
Community Champion
1 year ago
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
  Helper V
  1 year ago
  Shahid12523 Thanks, I will get back
v-echaithra
Community Support
1 year ago
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
  Helper V
  1 year ago
  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.

johnt75

Super User

1 year ago

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
Helper V
1 year ago
johnt75 thanks, i will get back; it look good.

v-echaithra
Community Support
1 year ago
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
  Helper V
  1 year ago
  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.

Forum Discussion

DAX - Average (measure) based on most recent Date

14 Replies

Related content

Rolling 12M Average measure

Average of measure

DAX Measure for Ranking the valid rows and calculating the average rating

Average of average measure

Weekly Average