Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Formula for Power Query

Good Morning Power BI community!   Im hoping to get some help with creating my formula, i have tried but keep getting errors.   This formula works on sharepoint list but does not work in BI:=IF(O...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    Ok. It's because your existing columns are in DateTime format, not date.

    Try this instead:

    let _todayDate = Date.From(DateTime.LocalNow()) in
if [Finding Type] = "Minor"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 5
then "Overdue"
else if [Finding Type] = "Major"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 1
then "Overdue"
else null

     

    Pete

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Good Morning,

I have inserted the updated calculation and now gettings errors, i have validated the errors and the condition is true on errors but not sure why its showing error instead of "Overdue". Where the condtion is not true its showing "null" as per condition. Thoughts as to why this can be?

 

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

Can you click in one of the error cells (not the word 'error' itself, just the space in the cell next to it) and let me know what the error message says please?

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Didn't know you could do that! Thanks that a top tip!

    Please see error message:

    Thanks for the quick response.

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      Ok. It's because your existing columns are in DateTime format, not date.

      Try this instead:

      let _todayDate = Date.From(DateTime.LocalNow()) in
if [Finding Type] = "Minor"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 5
then "Overdue"
else if [Finding Type] = "Major"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 1
then "Overdue"
else null

       

      Pete

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Pete, Thank you so much for your help, that did the trick! 

        I have learned some new tricks now.

        Thank you once again!