Forum Discussion
Calculating difference between two dates in weeks in an if statement
DATE1 =1/1/2020
DATE2 = 1/14/2020
Conditional Columns Created
PKG.1 = Table.AddColumn(#"Removed Columns", "PKG.1", each if [DATE2] = null then false else if [DATE1] = null then false else true)
Custom Column created but did not work. The result = 0.
Delay in Weeks = ( if[PKG.1] = "True" then Duration.Days(Duration.From[DATE2]-[DATE1]) / 7 else 0)
you could get the difference directly wothout thw intwrmwdiate add-column in this way and then raplace null with 0.
but if you want continue your way, try this change:
pay attention to the condition if [goodate] then also if [goodate]=true then would have been fine.
but if [goodate]="true" doesn't check what you intend i.e. the logical value true . This expression check instead the text "true" which is different value of true.
2 Replies
you could get the difference directly wothout thw intwrmwdiate add-column in this way and then raplace null with 0.
but if you want continue your way, try this change:
pay attention to the condition if [goodate] then also if [goodate]=true then would have been fine.
but if [goodate]="true" doesn't check what you intend i.e. the logical value true . This expression check instead the text "true" which is different value of true.
Thank you! the 2nd option worked for me. I have another multiple "if statement" that I'm having issues with.
PKG.1_DATE1 =1/1/2020
PKG.1_DATE2 = 1/14/2020
PKG.2_DATE1 = 1/2/2020
PKG.2_DATE2 = 1/20/2020
This worked
Custom Column -> Latest Package Delay = if List.Max({[PKG.1_DATE2], [PKG.2_DATE2]}) = 0 then null else List.Max({[PKG.1_DATE2], [PKG.2_DATE2]})
This does not work
Custom Column -> Schedule Delay = if [Latest Package Date] = null then 0 else if [Latest Package Date] = [PKG.1_DATE2] then (Duration.Days [PKG.1_DATE2]-[PKG.1_DATE1])/7 else if [Latest Package Date] = [PKG.2_DATE2] then (Duration.Days [PKG.2_DATE2]-[PKG.2_DATE1])/7 else 0