Date Difference of two columns with if then

[Sumit_Bhokare]

Hi,

Need one help to check difference between dates in power query.

I have spreadsheet within two columns for dates & based on difference between those days I have to show action point.

If diff >=2 but <5 then action pending

if diff >=5 but <7 then action overdue

if diff >=7 then Warning

can someone help me on this.

02-06-2022	30-05-2022	action pending
02-06-2022	31-05-2022	action pending
02-06-2022	01-06-2022
02-06-2022	02-06-2022
01-06-2022	03-06-2022
01-06-2022	04-06-2022
01-06-2022	05-06-2022
01-06-2022	06-06-2022
31-05-2022	07-06-2022
31-05-2022	08-06-2022
31-05-2022	09-06-2022
31-05-2022	10-06-2022
31-05-2022	11-06-2022
31-05-2022	12-06-2022

[Vijay_A_Verma · Accepted solution]

In a custom column use following formula

= [d=Number.Abs(Duration.Days([Date2]-[Date1])),
r=if d>=7 then "Warning" else
if d>=5 then "action overdue" else if d>=2 then "action pending" else null][r]

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BCsAgDETRu2QtJBlr2p5FvP81pIiklM4u/LeZ9C6hUBggRZpWW/coX/BfCGUd7+7ZK+kH6Y30yJ7rHjgZXAxuBm5UnMp+fEw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [d=Number.Abs(Duration.Days([Date2]-[Date1])),
r=if d>=7 then "Warning" else
if d>=5 then "action overdue" else if d>=2 then "action pending" else null][r])
in
    #"Added Custom"

[Sumit_Bhokare]

@Vijay_A_Verma Is there any easier way to calculate date difference excluding weekends? I'm trying alternative options but not working

[Vijay_A_Verma]

If you have a To date>=From date, use below formula, replace From and To

= List.Sum(List.Transform(List.Dates([From],Duration.Days([To]-[From])+1,#duration(1,0,0,0)), each Number.From(Date.DayOfWeek(_,1)<5)))

If you also have To date < From date, let me know. 

[Sumit_Bhokare]

@Vijay_A_Verma what needs to if I have To date < From date

[Vijay_A_Verma · Accepted solution]

In this case, you can use

-List.Sum(List.Transform(List.Dates([To],Duration.Days([From]-[To])+1,#duration(1,0,0,0)), each Number.From(Date.DayOfWeek(_,1)<5)))

Better to use below function which combines all > and < and = cases so no need to use different functions

[From=List.Min({[From],[To]}),To=List.Max({[From],[To]}),
Sign = if [From]>[To] then -1 else 1, 
Networkdays=Sign*List.Sum(List.Transform(List.Dates(From,Duration.Days(To-From)+1,#duration(1,0,0,0)), each Number.From(Date.DayOfWeek(_,1)<5)))][Networkdays]

Note - It doesn't handle blank To and From dates. Blank dates are treated as 30-Dec-1899 in Power BI. 

[Sumit_Bhokare]

Thank you for this 👍

[watkinnc]

I would write

Difference = let diff = each Number.From([Date1]) - Number.From([Date2]) in Table.AddColumn(PriorStepName, "Status", each if diff >6 then "Warning" else if diff > 4 then "overdue" else if diff > 1 then "action pending" else "new", type text)

--Nate

[Vijay_A_Verma · Accepted solution]

In a custom column use following formula

= [d=Number.Abs(Duration.Days([Date2]-[Date1])),
r=if d>=7 then "Warning" else
if d>=5 then "action overdue" else if d>=2 then "action pending" else null][r]

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BCsAgDETRu2QtJBlr2p5FvP81pIiklM4u/LeZ9C6hUBggRZpWW/coX/BfCGUd7+7ZK+kH6Y30yJ7rHjgZXAxuBm5UnMp+fEw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [d=Number.Abs(Duration.Days([Date2]-[Date1])),
r=if d>=7 then "Warning" else
if d>=5 then "action overdue" else if d>=2 then "action pending" else null][r])
in
    #"Added Custom"