Forum Discussion

Linnil's avatar
Linnil
Helper III
2 years ago
Solved

Let Statement (Step) with IF Statement

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 🙂

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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's avatar
      Linnil
      Helper III
      2 years ago

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