Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

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
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show thr expected result.


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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.