calculate duration of persons designation

[HassanAli123]

Hi,

    i have these columns in my data set and i want to calculate time period for a person's designation. for example Amanda in 2015 was on ED portfolios role till 2019. the duration should be "3 year and 3 months" of ED portfolios designation and for ED public services it should be from 2019 to till 2022 currently, like this for all employee. how to achieve it in DAX.
Thanks in advance

 

[amitchandak]

@HassanAli123 , Try like

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

[HassanAli123]

it is showing error " Cannot convert value  of type Text to type Date.

[amitchandak]

@HassanAli123 , Make sure effective from is a date

[amitchandak · Accepted solution]

@HassanAli123 ,

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"