Forum Discussion

cabadart's avatar
cabadart
Microsoft 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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
cabadart
Microsoft Employee
5 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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] )