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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous I believe you are mixing DAX and M code. You have M code if syntax but use DATEDIFF, which is a DAX function. Same thing in second example AddColumn, that's all M code until you get to DATEDIFF, which isn't a thing in M. Then your last example uses all DAX code so that's at least good. No idea what might be wrong with it. 

 

All of this said, since it looks like you just want the days between two dates, just subtract them. In DAX it is:

 

days = ( [Date1] - [Date2] ) * 1.

 

You can do something similar in M by converting the dates to numeric numbers and such.