Calculating difference between two dates in weeks in an if statement

[mpatzic]

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)

[Anonymous · Accepted solution]

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.

[mpatzic]

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