Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
HassanAli123
Helper I
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.
Thanks in advance

 Duration.PNG

1 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"

View solution in original post

4 REPLIES 4
amitchandak
Super User
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

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

@HassanAli123 , Make sure effective from is a date

@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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.