cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Average Salary as of Given Date

I am working with transactional data from an HR system. I am trying to create a measure that calculates the average salary of active employees at any given point in time from a date table.

The transactional data looks like this (simplified):

For the date 9/30/2021 (randomly chosen from the linked date table), the highlighted values should be included in the average salary calculation. EDITED: The expected result for this date is 68666.667. Gertrude's record on 8/1/2021 would be excluded since it is a termination.

Typically, I solve problems like this by creating a table (in power query) with every possible date for every possible employee. This seems terribly inefficient. I would prefer to find a dax measure that picks the right records and averages them.

I looked at several threads for inspiration. I tried to use CALCULATE and match the ID and effective date to variables, but I ended up using calculate in a True/False expression to filter a table, which is not allowed.

Here is my closest attempt so far. It does not account for termination records yet, but I think that could be added later without much difficulty. I think this should work:

Average Salary =

VAR vResult =
AVERAGEX(
VALUES ('Sample'[ID]),
VAR vUser = 'Sample'[ID]
VAR vMaxDate =

CALCULATE ( MAX ( 'Sample'[Effective Date]), 'Sample'[ID] = vUser )
VAR vSalary =
CALCULATE (
MAX ( 'Sample'[Salary] ),
'Sample'[ID] = vUser &&
'Sample'[Effective Date] = vMaxDate
)
RETURN
vSalary
)

RETURN
vResult

This returns an average, but only on effective dates where original transactional data lines up:

I need a measure that calculates the value for every date based on relevant records.

UPDATE:

I updated my calculation, but it still has the same issue (values only appear on days when transactional data has dates listed).

Average Salary =
VAR MaxDate = MAX (Dates[Date])
RETURN

CALCULATE(
AVERAGEX(
VALUES('Sample'[ID]),
CALCULATE(MAX('Sample'[Salary]),
'Sample'[ID] = EARLIER('Sample'[ID]),
'Sample'[Effective Date] <= MaxDate,
'Sample'[Effective Date] = MAX('Sample'[Effective Date])
)
),
'Sample'[Status] = "A"
)

1 ACCEPTED SOLUTION
Helper II

I was able to create a single measure that calculated the desired result with a linked date table:

Average Salary =

VAR MaxDate = MAX( Dates[Date] )
RETURN

AVERAGEX(
GROUPBY(
FILTER(
CALCULATETABLE(ALLSELECTED('Sample')) ,
//'Sample'[Status] = "A" &&
'Sample'[Effective Date] <= MaxDate
),
'Sample'[ID],
"EmpMaxDate",
MAXX(CURRENTGROUP(), 'Sample'[Effective Date]),
"EmpNo",
MAXX(CURRENTGROUP(),'Sample'[ID])
),
"CurrentRecordStatus",
LOOKUPVALUE(
'Sample'[Status],
'Sample'[Effective Date],[EmpMaxDate],
'Sample'[ID],[EmpNo])
),
"CurrentRecordSalary",
LOOKUPVALUE(
'Sample'[Salary],
'Sample'[Effective Date],[EmpMaxDate],
'Sample'[ID],[EmpNo],
'Sample'[Status],[CurrentRecordStatus]
)
),
"NextEffective",
VAR EmpNo = [EmpNo]
VAR ED = [EmpMaxDate]
RETURN
CALCULATE(
MIN('Sample'[Effective Date]),
ALL('Sample'),
'Sample'[ID] = EmpNo &&
'Sample'[Effective Date] > ED
)
),
IF(
[CurrentRecordStatus] = "A" &&
([NextEffective] > MaxDate || ISBLANK([NextEffective])),
[CurrentRecordSalary],
BLANK()
)
)

However, the filter behavior for this table did not meet my needs (clicking on a specific month did not filter down to the relevant records in other visuals), so I ended up abandoning this approach. My final solution was to add a "next effective date" field to transactional data in power query, crossjoin the transactional table with the date table in DAX, and then filter that table down to the desired date range to save some space. This is slow, but much faster than attempting the same in power query, and it achieves the desired filtering behavior in my report.

@v-rongtiep-msft - thank you for your help. Your answers helped me think through the problem and got me closer to my own solution.

4 REPLIES 4
Helper II

I was able to create a single measure that calculated the desired result with a linked date table:

Average Salary =

VAR MaxDate = MAX( Dates[Date] )
RETURN

AVERAGEX(
GROUPBY(
FILTER(
CALCULATETABLE(ALLSELECTED('Sample')) ,
//'Sample'[Status] = "A" &&
'Sample'[Effective Date] <= MaxDate
),
'Sample'[ID],
"EmpMaxDate",
MAXX(CURRENTGROUP(), 'Sample'[Effective Date]),
"EmpNo",
MAXX(CURRENTGROUP(),'Sample'[ID])
),
"CurrentRecordStatus",
LOOKUPVALUE(
'Sample'[Status],
'Sample'[Effective Date],[EmpMaxDate],
'Sample'[ID],[EmpNo])
),
"CurrentRecordSalary",
LOOKUPVALUE(
'Sample'[Salary],
'Sample'[Effective Date],[EmpMaxDate],
'Sample'[ID],[EmpNo],
'Sample'[Status],[CurrentRecordStatus]
)
),
"NextEffective",
VAR EmpNo = [EmpNo]
VAR ED = [EmpMaxDate]
RETURN
CALCULATE(
MIN('Sample'[Effective Date]),
ALL('Sample'),
'Sample'[ID] = EmpNo &&
'Sample'[Effective Date] > ED
)
),
IF(
[CurrentRecordStatus] = "A" &&
([NextEffective] > MaxDate || ISBLANK([NextEffective])),
[CurrentRecordSalary],
BLANK()
)
)

However, the filter behavior for this table did not meet my needs (clicking on a specific month did not filter down to the relevant records in other visuals), so I ended up abandoning this approach. My final solution was to add a "next effective date" field to transactional data in power query, crossjoin the transactional table with the date table in DAX, and then filter that table down to the desired date range to save some space. This is slow, but much faster than attempting the same in power query, and it achieves the desired filtering behavior in my report.

@v-rongtiep-msft - thank you for your help. Your answers helped me think through the problem and got me closer to my own solution.

Community Support

I hvae created a simple smaple, please refer to it to see if it helps you.

Create two measures.

Measure =
VAR _selected =
SELECTEDVALUE ( Dates[Date] )
VAR _macdate =
CALCULATE (
MAX ( 'Sample'[Effective Date] ),
FILTER (
ALL ( 'Sample' ),
'Sample'[Name] = SELECTEDVALUE ( 'Sample'[Name] )
&& 'Sample'[Effective Date] <= _selected
)
)
VAR _status =
CALCULATE (
MAX ( 'Sample'[Status] ),
FILTER ( ALL ( 'Sample' ), 'Sample'[Effective Date] = _macdate )
)
VAR _salary =
CALCULATE (
MAX ( 'Sample'[Salary] ),
FILTER ( ALL ( 'Sample' ), 'Sample'[Effective Date] = _macdate )
)
VAR _tstates =
IF (
_status = "A"
&& MAX ( 'Sample'[Effective Date] ) = _macdate,
_salary,
BLANK ()
)
RETURN
_tstates
average =
VAR _1 =
CALCULATE (
DISTINCTCOUNT ( 'Sample'[Name] ),
FILTER ( ALL ( 'Sample' ), [Measure] <> BLANK () )
)
VAR _sum =
SUMX ( FILTER ( ALL ( 'Sample' ), [Measure] <> BLANK () ), [Measure] )
RETURN
_sum / _1

Best Regards

Community Support Team _ Polly

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

Helper II

I discovered that you disconnected the date table from the transactional data in the file you sent. When I reconnect the date table to effective date, the measure breaks. Do you have a solution for this that does not require the date table to be separated?

Also, your current solution does not give the desired output when two active pay records coincide on the same date - the measure named "measure" takes the highest of all available options. To illustrate, I added a record to the sample data file so that two separate individuals have a record on the same day. The correct output would be 50,500, not 63,000:

Thank you!

Community Support
1. disconnect the date table from the transactional data. The only one way I have: create another calendar table. If you want to achieve that, you must need a calendar table without relationship.
2. Modify the measure.
Measure =
VAR _selected =
SELECTEDVALUE ( Dates[Date] )
VAR _macdate =
CALCULATE (
MAX ( 'Sample'[Effective Date] ),
FILTER (
ALL ( 'Sample' ),
'Sample'[Name] = SELECTEDVALUE ( 'Sample'[Name] )
&& 'Sample'[Effective Date] <= _selected
)
)
VAR _status =
CALCULATE (
MAX ( 'Sample'[Status] ),
FILTER ( ALL ( 'Sample' ), 'Sample'[Effective Date] = _macdate )
)
VAR _salary =
CALCULATE (
MAX ( 'Sample'[Salary] ),
FILTER (
ALL ( 'Sample' ),
'Sample'[Effective Date] = _macdate
&& 'Sample'[Name] = SELECTEDVALUE ( 'Sample'[Name] )
)
)
VAR _tstates =
IF (
_status = "A"
&& MAX ( 'Sample'[Effective Date] ) = _macdate,
_salary,
BLANK ()
)
RETURN
_tstates

Best Regards

Community Support Team _ Polly

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors