Forum Discussion
Microsoft EmployeeAge 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). So if an employee started working in 2018 Q4 their age would show as:
| Year | Quarter | Age |
| 2019 | Qtr 3 | 0 |
| 2019 | Qtr 4 | 1 |
| 2020 | Qtr 1 | 1 |
| 2020 | Qtr 2 | 1 |
| 2020 | Qtr 3 | 1 |
| 2020 | Qtr 4 | 2 |
| 2021 | Qtr 1 | 2 |
| 2021 | Qtr 2 | 2 |
I used the built-in Age functionality however that just does DateTimeZone.LocalNow() - [HireDate] so it shows the same value for every quarter. I also want to show this graph of the % of employees who are "Early in Career" (their Age is 5 or less) each quarter. Currently it only shows people as early in career if their HireDate was in the last 5 years. But if someone was hired 6 years ago, they should as Early in Career for the next 5 years after their HireDate, but not in the last 4 quarters.
This is the chart I have so far, but it's wrong.
This is how I calculated my Age:
#"Inserted Age" = Table.AddColumn(Source, "Age", each DateTimeZone.LocalNow() - [FirstRegularHireDate]),
#"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
#"Rounded Down" = Table.TransformColumns(#"Calculated Total Years",{{"Age", Number.RoundDown, Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Rounded Down",{{"Age", "AgeRoundedDown"}})
And IsEarlyInCareer = [AgeRoundedDown] <= 5
I just don't know how to make the end date of the Age calculation be the end of the quarter, rather than the current datetime.
Thanks!
6 Replies
- amitchandak
Super User
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.
cabadartamitchandak 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...
v-yingjlCommunity Support
Hi cabadart ,
If there is a relationship between your date table and source table, basically the dax formula should work.
Or you can try to create the measure like this:
_Count = COUNTX ( FILTER ( 'HireDate', DATEDIFF ( 'HireDate'[HireDate], MAX ( 'Calendar'[Date] ), YEAR ) <= 5 ), [Alias] )Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi cabadart
Do you have a Date column then you can apply End of Quarter?
If you are using quarter numbers, alternatively, you can round the number using Year&Quarter (or Year * 10 + Quarter), I am using all text to show you the example
Table.AddColumn(#"Inserted End of Quarter", "Custom", each Number.RoundTowardZero ( ( Number.From( Text.From([Year]) & Text.Select([Quarter],{"1".."4"})) - 20184)/10))- cabadart
Anonymous I do have a calendar table that I made just using CALENDARAUTO() and there is a relationship between my Calendar and HireDate table. I am not sure how I am supposed to use your suggestion in defining my measure?
Hi cabadart
I saw you using M...so you only count Age <=5? The calendar table has no relationship with your fact table? Try it
test = VAR CurDate = MAX ( 'Calendar'[Date] ) VAR T1 = ADDCOLUMNS ( Table, "COUNT", VAR Age = DATEDIFF ( Table[Hiredate], CurDate, YEAR ) RETURN IF ( Age <= 5 && Age > 0, 1, 0 ) ) RETURN SUMX ( T1, [COUNT] )
