Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone - I'm just learning how to make Let Statements as Steps.
I have this problem - I'm calculating Years of Service, at 30 Apr 23.
However, if the person has left / if Status is T, then the endDate = Term Date.
Is it possible to modify the statement below so it does a check for the endDate ?
This is my Let Statement (as a Step) so far:
let
startDate = [Hire Date],
endDate = #date(2023,04,30),
durationInDays = Duration.Days(endDate - startDate),
durationInYears = durationInDays / 365
in
durationInYears
I thought briefly about doing it as a conditional column, I think that's another complication.
Thanks for any suggestions 🙂
Solved! Go to Solution.
Hi @Linnil
Assuming you have a [Status] and [Term Date] field, like [Hire Date] in in your table, try this:
let
startDate = [Hire Date],
endDate = if [Status] = "T" then [Term Date] else #date(2023,04,30),
durationInDays = Duration.Days(endDate - startDate),
durationInYears = durationInDays / 365
in
durationInYears
I hope this is helpful
Hi @Linnil
Assuming you have a [Status] and [Term Date] field, like [Hire Date] in in your table, try this:
let
startDate = [Hire Date],
endDate = if [Status] = "T" then [Term Date] else #date(2023,04,30),
durationInDays = Duration.Days(endDate - startDate),
durationInYears = durationInDays / 365
in
durationInYears
I hope this is helpful
Thank you for this! (sorry for late reply, it was a crazy week).
Have a lovely weekend 🙂