cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v_mark
Helper III
Helper III

Two Criteria's in calculating Durations in days?

I was trying to calculate the duration in days with two criteria. The goal is to get the days with these conditions below.

Just not sure if there is a way to calculate this in power query inside a custom column. 

 

1 . IF the status is "Closed" calculate the Duration.Days(Duration.From( [ ClosedDateTime ] - [ CreatedDateTime ] ).
2.  IF the status is "New/Active" calculate the Date.From(DateTime.LocalNow()) - [CreatedDateTime]

 

ClosedDateTimeCreatedDateTimeNew StatusResults
2021-04-04T07:11:15.97300002021-04-01T01:38:59.8970000Closed 
2021-05-05T20:57:07.93300002021-05-05T20:35:26.8800000Closed 
2021-05-12T11:52:35.04300002021-05-12T11:35:18.1670000Closed 
1/1/1900 12:00:00 AM2021-03-31T19:17:28.5370000New / Active 
1/1/1900 12:00:00 AM2021-05-04T16:54:19.2300000New / Active 
1/1/1900 12:00:00 AM2021-05-08T15:59:02.9330000New / Active 
1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DsIwDIRfJepcgs+pm8RbxQxTNtQJOiAhMYDg9TGC8icQEvZtZ3863XJZMTEm1JgKRQUU4nMMZFPVDxeFoCGpZJ9yvLmz7W4/rKu+HjFiKkwqUSn6HF4xoxtEufUp0XcMuFgUYbv11Lxjrq5hkDzaD2kwtc1EDqxEJtfN7/9hElCQFVE5eQnj/2I4uanrVofNcfhNkUtjaFUaRfYc6F9KKhCrVYmfCnul9Gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedDateTime = _t, CreatedDateTime = _t, #"New Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClosedDateTime", type datetime}, {"CreatedDateTime", type datetime}, {"New Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Record.FieldOrDefault([Closed = Duration.Days([ClosedDateTime]-[CreatedDateTime]), #"New / Active" = Duration.Days(DateTime.LocalNow()-[CreatedDateTime])], [New Status], "other"))
in
    #"Added Custom"

Screenshot 2021-05-13 231029.png

View solution in original post

3 REPLIES 3
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DsIwDIRfJepcgs+pm8RbxQxTNtQJOiAhMYDg9TGC8icQEvZtZ3863XJZMTEm1JgKRQUU4nMMZFPVDxeFoCGpZJ9yvLmz7W4/rKu+HjFiKkwqUSn6HF4xoxtEufUp0XcMuFgUYbv11Lxjrq5hkDzaD2kwtc1EDqxEJtfN7/9hElCQFVE5eQnj/2I4uanrVofNcfhNkUtjaFUaRfYc6F9KKhCrVYmfCnul9Gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedDateTime = _t, CreatedDateTime = _t, #"New Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClosedDateTime", type datetime}, {"CreatedDateTime", type datetime}, {"New Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Record.FieldOrDefault([Closed = Duration.Days([ClosedDateTime]-[CreatedDateTime]), #"New / Active" = Duration.Days(DateTime.LocalNow()-[CreatedDateTime])], [New Status], "other"))
in
    #"Added Custom"

Screenshot 2021-05-13 231029.png

View solution in original post

@CNENFRNL Thank you. I was trying to digest the last part on how it will be written in M. Is this something you can show , please?

As there's no SWITCH() statement in M language, Record.FieldOrDefault() is an alternative.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors