cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISUGraber Member
Member

Calculate 12 Month Wages for Terminated and Current Employees

Hi everyone,

I have a data model where I Have an Employee table that has every employee in it.  That table has employee name, hire date, terminated date and a iscurrent field telling if they are currently employed now or not.  Term date is null if they are still employed.

 

I then have that table linked to a wages file by employee ID that contains their historical earnings.  

 

I have the following DAX Code that allows me to calculate the last 12 months of pay for current employees.  However, it doesn't work for people that left in the past since this goes from today until 12 months ago.  Is there a way I can modify this DAX to use the terminated date field in the employee table to get the last 12 months of pay from the day they left?

 

var varToday =  Today()
var varYearAgo = varToday - 365
RETURN


CALCULATE(
    SUM(Earnings[EarningsAmount]),
    FILTER('Earnings',
 'Earnings'[Date] <= varToday
 && 'Earnings'[Date]>= varYearAgo
))
1 ACCEPTED SOLUTION

Accepted Solutions
amitchandak Super Contributor
Super Contributor

Re: Calculate 12 Month Wages for Terminated and Current Employees

Can you build the filter on the Terminate date and if it is blank then use Today as the date

View solution in original post

2 REPLIES 2
amitchandak Super Contributor
Super Contributor

Re: Calculate 12 Month Wages for Terminated and Current Employees

Can you build the filter on the Terminate date and if it is blank then use Today as the date

View solution in original post

Super User
Super User

Re: Calculate 12 Month Wages for Terminated and Current Employees

Hi,

Share some data and show thr expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 208 members 2,089 guests
Please welcome our newest community members: