Trying to create a measure that provides Gap between employee wage and minimum wage

[Mike_12]

Hello,

I am trying to create a measure that subtracts the minimum wage from the wage of a termed employee.   I am trying to see if a higher gap in pay correlated to longer employment length. The measure will need to reference the employee termination date column in the employee records table (will be blank if the employee is active) and find the correct min wage rate in another table.

I have two tables:

   Table 1: Employee Records - Columns used are [termination date] and [pay rate]

   Table 2: MinWage - Columns [MW Date Low], [MW Date High], [MinWageRate]

                Each row contains the start date, end date, and min wage rate

Measures already created: 

   Selected MW Date High = MAX('Min Wage Table'[MW Date High])

   Selected MW Date Low = MIN('Min Wage Table'[MW Date Low])

I am looking to calculate [Pay Rate] - [MinWageRate] for each employee.  I would then create an average calculation also.  

Any help would be much appreciated!

[v-xulin-mstf · Accepted solution]

Hi @Mike_12

Try measure as:

minimum wage = 
CALCULATE(
    MAX('Min Wage'[Min Wage]),
    FILTER(
        'Min Wage',
        'Min Wage'[Date High]>MAX(Employee[Date]) && 'Min Wage'[Date Low]<MAX(Employee[Date])
    )
)

Best Regards,

Link 

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

[edhans]

You probably want to use AVERAGEX. It will  work over a table and average the data. So:

Average Amount =
AVERAGEX(
    'TableName',
    'TableName'[Pay Rate]
        - MINX(
            RELATEDTABLE( 'Min Wage Table' ),
            'Min Wage Table'[MW Date Low]
        )
)

But without actual data, hard to really help and validate.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Mike_12]

Thanks again for your time and help with this.  I really appreciate it.  However, it still does not provide me with what I am after.  This is my fault, as I believe I have not explained things properly.

Here is an example of the employee table:

Employee	Wage	Termination Date
Jack	11	2/1/2009
Jill	12.5	5/6/2020

I need a measure that will determine the state minimum wage at the time of their termination.

I created the below measure which returns an accurate head count

   CALCULATE([Term Head Count (Date Based)] ,                FILTER(Sharepoint_Employees,Sharepoint_Employees[TerminationDate]>= [Selected MW Date Low] && Sharepoint_Employees[TerminationDate] < [Selected MW Date High]))

However, I can't figure out how to create a measure that subtracts the state minimum wage from the employee's wage.

From the table above, I need it to subtract the $8.25 min wage from each of the 5,394 employees' wages termed during the period when the state minimum wage was $8.25.

Example: 

               Employee Table:  Jack was termed on 2/1/2009.  His wage was $9.25. 

               Min Wage Table:  7/1/2008 to 7/1/2009, the state minimum wage was $7.75.

               9.25-7.75= 1.50

Again, I really appreciate the time and effort.

[Mike_12]

I appreciate your reply.  The measure did not work, but I believe this is my fault for not providing data.  Here is the Min Wage Table:

Date Low	Date High	Min Wage
1/1/2005	7/1/2007	$      6.50
7/1/2007	7/1/2008	$      7.50
7/1/2008	7/1/2009	$      7.75
7/1/2009	7/1/2010	$      8.00
7/1/2011	1/1/2020	$      8.25
1/1/2020	7/1/2020	$      9.25
7/1/2020	1/1/2021	$    10.00
1/1/2021	1/1/2022	$    11.00
1/1/2022	1/1/2023	$    12.00
1/1/2023	1/1/2024	$    13.00
1/1/2024	1/1/2025	$    14.00
1/1/2025	1/1/2030	$    15.00

I need a measure that can use the employee termination date and match it to the appropriate min wage rate during that time, then subtract the min wage from the employee wage.  

Example:  Employee - Termed 8/1/2009 - wage $9.25

                 Employee Wage - Min Wage = $9.25 - $8.00 = $1.24 Wage Gap

I want to use the measure for wage gap analysis.  In 2011 when min wage was 8.25 and we had an average $2.00 wage gap, our avg employee length of employment was....... In 2020 when min wage was 10.00 and we had a .50 wage gap our avg length of employment was...

I already have the employment length measures, I just need a measure to provide the wage gap

[edhans]

@Mike_12 take a look at the PBIX file I've linked to. It returns this:

To get this I did a bit of modeling, so look in Power Query. I transformed your Date Range table from this:

to this:

then set the model up like this:

The DAX then becomes really easy.

My PBIX file is here. 

[Mike_12]

Also, I am trying to do this without calculated columns.