Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
@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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |