Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

calculate employee position Duration

Hi, i am posting this again. i have a table and i want to calculate time period for a person's current position mean how much time he spent on the specific role. for example Amanda in 2015 was on ED...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , That will not add up 

     

    New column =
    var _min = minx(filter(Table, [Employee Name] = earlier([Employee Name]) && [Effective from] > earlier([Effective from])), [Effective from])

    var _diff = datediff([Effective from], coalesce(_min, today()), month)
    return Quotient(_diff ,12) & " Years " & Mod(_diff ,12)  & " Months"

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

want to check why this did not work

 


New column =
var _min = minx(filter(Table, [Employee Name] = earlier([Employee Name]) && [Effective from] > earlier([Effective from])), [Effective from])
return
datediff([Effective from], coalesce(_min, today()), month)/12.0

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi amitchandak 
    it worked thanks but the output is showing like 8.02 and 2.08. i want this like 8years and 2months in this format
    Thanks again if you help me to get this format

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      Anonymous , That will not add up 

       

      New column =
      var _min = minx(filter(Table, [Employee Name] = earlier([Employee Name]) && [Effective from] > earlier([Effective from])), [Effective from])

      var _diff = datediff([Effective from], coalesce(_min, today()), month)
      return Quotient(_diff ,12) & " Years " & Mod(_diff ,12)  & " Months"

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        it is throwing this error.