Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create a new column and use DateDiff function with multiple IF statements?

I am very new to Power BI and trying to create a new column Aging and Logic will be   Logic 1: IF the column Proposed Complete Date does not contain Good and ART and 2021 and 2020 then DATEDIFF([T...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Aging =

    Table.AddColumn(#"Duplicated Column1", "Aging", each if not Text.Contains([Proposed Complete Date],"Good") and not Text.Contains([Proposed Complete Date],"2021")and not Text.Contains([Proposed Complete Date],"2020") and not Text.Contains([Proposed Complete Date],"ART") then Duration.TotalDays([Today Date]-[Request Date]) else "")

     

    Logic 2:

     

    if [Sliped Date] > #date(2019,12,31) then Duration.TotalDays([Sliped Date]-[Request Date]) else [Aging]

     

    --Nate

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous I have tried your query and it didn't work it came out as an error

 

Table.AddColumn(#"Replaced Errors1", "Aging", each if not Text.Contains([Proposed Complete Date],"Good") and not Text.Contains([Proposed Complete Date],"2021")and not Text.Contains([Proposed Complete Date],"2020") and not Text.Contains([Proposed Complete Date],"ART") then Duration.TotalDays([Today Date]-[Request Date]) else if [Sliped Date] > #date(2019,12,31) then Duration.TotalDays([Sliped Date]-[Request Date]) else "")

 

Below is the screenshot

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous I found the issue. My columns type was text instead of the date that's why I was getting errors. Thanks for your help. Really appreciate it.