Average Salary as of Given Date

[sean_cochran]

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"
    )

.pbix with sample data and updated calculation available here: https://drive.google.com/file/d/1jkUxomEWU5aADO9yoYDflgoMBEi8EZ84/view?usp=sharing

[sean_cochran · Accepted solution]

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(
        ADDCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        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.

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

[Anonymous]

Hi @sean_cochran ,

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

If I hvae misunderstood your meaning, please provide more details with your desired output.

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.

[sean_cochran]

@Anonymous ,

Thank you for your help!!

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!

[Anonymous]

Hi @sean_cochran ,

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.