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

Hi Anonymous ,

 

Your text description translates into Power Query M something like this:

 

let _todayDate = Date.From(DateTime.LocalNow()) in
if [Finding Type] = "Minor"
    and [Containment] = null
    and Duration.Days(_todayDate - [Created]) > 5
then "Overdue"
else if [Finding Type] = "Major"
    and [Containment] = null
    and Duration.Days(_todayDate - [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?