Forum Discussion

cabadart's avatar
cabadart
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Age defined by quarter

I am making a report based on employee data.  One thing I have not been able to figure out is how to calculate an employees "Age" at the company each quarter (based off of their HireDate datetime).  ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

cabadart , Looking at this seem like you need a measure

 

countx(values(employee [Employee ID]), if(datediff(employee[Hiredate]<maxx('date'[date]),year)<=5, [Employee ID], blank()))

 

You should plot this with qtr, year from date table  joined to hire date of the employee table 

 

I attached my HR demo file, if that can help

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

cabadart's avatar
cabadart
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

amitchandak the measure you gave me is not quite right - it says "A single value for column 'HireDate' in table 'HireDate' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

 

I added MIN so my measure looks like this:

COUNTX(VALUES(HireDate[Alias]), IF(DATEDIFF(MIN(HireDate[HireDate]), MAXX('Calendar', [Date]), YEAR) <= 5, [Alias], blank()))
 
But I'm not confident that this is doing what I want it to do, the numbers look very low.  I looked in the Current_employee.pbix file you attached, but not see an analogous measure definition in there.  For instance, using my existing Age column I know that there are 255 Early in Career employees currently (2021 Qtr 1) - but the measure in the table is showing that there are only 19...