Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
@watkinnc 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
@watkinnc 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.
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)
)
)
)
)
Do you want this in Power Query (M) or DAX?
Preference is Power Query (M).
@manjarigoyal 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.