Forum Discussion
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([Today Date],[Request Date], DAY)
Logic 2:
IF [Sliped Date] > "2019-12-31" then DateTimeDiff([Sliped Date],[Request Date],"days") else [Aging]
I tried few ways but so far I didn't get any results. All of these are errors out.
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 DATEDIFF([Today Date],[Request Date],"DAY") else "")
Aging =
SWITCH(TRUE(),not(CONTAINSSTRING('Request Status'[Proposed Complete Date],"Good")),DATEDIFF([Today Date],[Request Date], DAY),not(CONTAINSSTRING('Request Status'[Proposed Complete Date],"2021")),DATEDIFF([Today Date],[Request Date], DAY),not(CONTAINSSTRING('Request Status'[Proposed Complete Date],"2020")),DATEDIFF([Today Date],[Request Date], DAY),not(CONTAINSSTRING('Request Status'[Proposed Complete Date],"ART")),DATEDIFF([Today Date],[Request Date], DAY),0)
Thanks for the help.
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
7 Replies
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.
I got the DAX formula working but still couldn't figure it out in M
Aging =
IF
('Request Status'[Proposed Complete Date] = "Good",BLANK(),
IF
('Request Status'[Proposed Complete Date] = "ART",BLANK(),
IF
(CONTAINSSTRING('Request Status'[Proposed Complete Date],"2021"), BLANK(),
IF
(CONTAINSSTRING('Request Status'[Proposed Complete Date],"2020"), BLANK(),DATEDIFF([Today Date],[Request Date], DAY)
)
)
)
)
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 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
