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

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! 

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    No problem, happy to help 🙂

    Don't forget to give a thumbs-up on any posts that have helped you 👍

     

    Pete