cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## calculate duration of persons designation

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.

1 ACCEPTED SOLUTION
Super User

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"

4 REPLIES 4
Super User

@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

Helper I

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

Super User

@HassanAli123 , Make sure effective from is a date

Super User

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"