Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
93 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
107 | |
98 | |
92 |