Problem with date calculations on several columns

[jtomczyk]

Hi,

I have several columns with different dates. Each column represent step in the production and the date there accomplished task. I would like to check what is the latest step in the production my projects are right now.

I wrote in custom column such condition

if [T6]<=Date.From(DateTime.LocalNow()) then 6 else
if [T5]<=Date.From(DateTime.LocalNow()) then 5 else
if [T4]<=Date.From(DateTime.LocalNow()) then 4 else
if [T3]<=Date.From(DateTime.LocalNow()) then 3 else
if [T2]<=Date.From(DateTime.LocalNow()) then 2 else 1

As a result I get errors and step 5 or 6 only.

I have checked the date formats and everywhere I have YYYY-MM-DD format

The problem is not all projects go with all steps so in many colums we have null values.

Can you please propose a solution for that?

[BeaBF · Accepted solution]

@jtomczyk  Hi! The simplest way is to add a condition to your code, like this:

if [T6]<=Date.From(DateTime.LocalNow()) and [T6] <> null then 6 else
if [T5]<=Date.From(DateTime.LocalNow()) and [T5] <> null then 5 else
if [T4]<=Date.From(DateTime.LocalNow()) and [T4] <> null then 4 else
if [T3]<=Date.From(DateTime.LocalNow()) and [T3] <> null then 3 else
if [T2]<=Date.From(DateTime.LocalNow()) and [T2] <> null then 2 else 1

Try it!

B.

[jtomczyk]

Thanks!

[Vera_33 · Accepted solution]

Hi @jtomczyk 

Another way, have a try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR1UDhwZmugbmMJEDQ30gQjMidUBajdC1o7MMbLQN7DENAtNuzGydmOCtivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, T1 = _t, T2 = _t, T3 = _t, T4 = _t, T5 = _t, T6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"T2", type date}, {"T3", type text}, {"T4", type text}, {"T5", type date}, {"T6", type date}, {"T1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 6 - List.PositionOf(
 List.Transform( {[T6],[T5],[T4],[T3],[T2],[T1]}, each try _ <= Date.From( DateTime.LocalNow()) otherwise false), true))
in
    #"Added Custom"

[BeaBF · Accepted solution]

@jtomczyk  Hi! The simplest way is to add a condition to your code, like this:

if [T6]<=Date.From(DateTime.LocalNow()) and [T6] <> null then 6 else
if [T5]<=Date.From(DateTime.LocalNow()) and [T5] <> null then 5 else
if [T4]<=Date.From(DateTime.LocalNow()) and [T4] <> null then 4 else
if [T3]<=Date.From(DateTime.LocalNow()) and [T3] <> null then 3 else
if [T2]<=Date.From(DateTime.LocalNow()) and [T2] <> null then 2 else 1

Try it!

B.