Let Statement (Step) with IF Statement

[Linnil]

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 🙂

[m_dekorte · Accepted 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

[Linnil]

Thank you for this! (sorry for late reply, it was a crazy week).
Have a lovely weekend 🙂