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...
  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    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.