Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sean_cochran
Helper II
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):

sean_cochran_0-1666205361670.png

 

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:

 

sean_cochran_0-1666197761910.png

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

 

1 ACCEPTED SOLUTION
sean_cochran
Helper II
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(
        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.

 

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

View solution in original post

4 REPLIES 4
sean_cochran
Helper II
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(
        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.

 

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

v-rongtiep-msft
Community Support
Community Support

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

 

 

vpollymsft_0-1666231189419.png

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.

@v-rongtiep-msft ,

 

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:

updated sample.jpg

Thank you!

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

 

 

vpollymsft_0-1666314811734.pngvpollymsft_1-1666314822314.png

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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