Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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"
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.
@Vijay_A_Verma Is there any easier way to calculate date difference excluding weekends? I'm trying alternative options but not working
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.
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.
Thank you for this 👍
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
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"