calculate datedifference between two dates and in consecutive order

[Anonymous]

Dear community:

I would like to calculate the date difference between the two dates per ID level, in consecutive order. in the same order no, second date - first date, then third date - second date, then fourth date - third date.. till the max date - the second max date....  there is no clear idea of how many records will be per each order No. 

here is the sample data. any idea how to do that? thank you.

Order No	start date	due date	Days difference
1234	4/10/2021	4/13/2021
1234	4/15/2021	4/18/2021	=datediff(4/18/2021 - 4/13/2021)
1234	5/1/2021	5/12/2021	=datediff(5/12/2021 - 4/18/2021)
1234	5/17/2021	5/21/2021	same logic as above
1235	3/12/2021	5/1/2021	same logic as above
1235	4/12/2021	5/3/2021	same logic as above
1235	5/2/2021	5/10/2021	same logic as above
1235	5/8/2021	5/16/2021	same logic as above

Thank you so much 

[selimovd · Accepted solution]

Hey @Anonymous ,

from my opinion the easiest way is in Power Query. As it is part of the transformation I also think that's the right spot to do the transformation.

You can do that with an Index column and then you take the value from the last row and do your calculations.

Check my example in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLRNzTQNzIwMoSwjWFsBaVYHWRFpkiKLGBs25TEktSUzLQ0Dbiogq4C3BhNZDNM9Q1h2oBMI0wj4KIQIyywGmGOMMMIbl5xYm6qQk5+emayQmKxQmJSflkqTJspUNoYyTokVxDQZYKiy5hIXUBnIVllQLQuCyRdZnh1xQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, #"start date" = _t, #"due date" = _t, #"Days difference" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"start date", type text}, {"due date", type text}, {"Days difference", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Add Value Last Row" = Table.AddColumn(#"Added Index", "due date last row", each if [Index] > 0 then #"Added Index"{ [Index] -1 }[due date] else 0),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Add Value Last Row", {{"start date", type date}, {"due date", type date}, {"due date last row", type date}}, "en-US"),
    #"Add Oder No Last Row" = Table.AddColumn(#"Changed Type with Locale", "Order No last row", each if [Index] = 0 then [Order No] else #"Added Index"{ [Index] -1 }[Order No] ),
    #"Added DaysDifference" = Table.AddColumn(#"Add Oder No Last Row", "DaysDifference", each if [Index] <> 0 and [Order No] = [Order No last row] then Duration.Days([due date] - [due date last row]) else null ),
    #"Removed Columns" = Table.RemoveColumns(#"Added DaysDifference",{"Index", "due date last row", "Order No last row"})
in
    #"Removed Columns"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd @Ashish_Mathur  It is great!!! I've learned two different ways to solve this. very appreciated~~

[selimovd]

Hey @Anonymous ,

that's great news!

Now you know how to solve it in Power Query and also in DAX.

Don't forget to mark both as solution. Like this the next person who is reading the post knows which approaches work.

Best regards

Denis

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[due date]),FILTER(Data,Data[Order No]=EARLIER(Data[Order No])&&Data[due date]<EARLIER(Data[due date])))),BLANK(),1*(Data[due date]-CALCULATE(MAX(Data[due date]),FILTER(Data,Data[Order No]=EARLIER(Data[Order No])&&Data[due date]<EARLIER(Data[due date])))))

Hope this helps.

[selimovd · Accepted solution]

Hey @Anonymous ,

from my opinion the easiest way is in Power Query. As it is part of the transformation I also think that's the right spot to do the transformation.

You can do that with an Index column and then you take the value from the last row and do your calculations.

Check my example in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLRNzTQNzIwMoSwjWFsBaVYHWRFpkiKLGBs25TEktSUzLQ0Dbiogq4C3BhNZDNM9Q1h2oBMI0wj4KIQIyywGmGOMMMIbl5xYm6qQk5+emayQmKxQmJSflkqTJspUNoYyTokVxDQZYKiy5hIXUBnIVllQLQuCyRdZnh1xQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, #"start date" = _t, #"due date" = _t, #"Days difference" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"start date", type text}, {"due date", type text}, {"Days difference", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Add Value Last Row" = Table.AddColumn(#"Added Index", "due date last row", each if [Index] > 0 then #"Added Index"{ [Index] -1 }[due date] else 0),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Add Value Last Row", {{"start date", type date}, {"due date", type date}, {"due date last row", type date}}, "en-US"),
    #"Add Oder No Last Row" = Table.AddColumn(#"Changed Type with Locale", "Order No last row", each if [Index] = 0 then [Order No] else #"Added Index"{ [Index] -1 }[Order No] ),
    #"Added DaysDifference" = Table.AddColumn(#"Add Oder No Last Row", "DaysDifference", each if [Index] <> 0 and [Order No] = [Order No last row] then Duration.Days([due date] - [due date last row]) else null ),
    #"Removed Columns" = Table.RemoveColumns(#"Added DaysDifference",{"Index", "due date last row", "Order No last row"})
in
    #"Removed Columns"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd may i ask why you add #"Added Index" in front of the if-then statement? thanks in advance

[selimovd]

Hey @Anonymous ,

did you try the solution?

This is using the result from the step before (#"Added Index") is navigating to the previous row ([Index] -1) and is then selecting the column ([due date] or [Order No]) from that row.

Like this you get the value from the last row.

Does that make sense?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

yes, I tried and it worked perfectly, but just not understood why. I never use such a query before to call another value using the index from the same row. great thank you 🙂
so does it mean #" Added index" is to call previous step, {[index] - 1} is the position where should refer to, [due date] is value need to be shown in the current step.

[selimovd]

Hey @Anonymous ,

yes, that's a really nice way. Actually {[index] - 1} is just a dynamic way to get the row. You could also write {4} to get the 4th row of the step that you mentioned. For example the the value from the 4th row from the Index column:

= Table.AddColumn(#"Add Value Last Row", "IndexColumn 4th value", each #"Add Value Last Row"{ 4 }[Index] )

And you can also refer to all the steps in Power Query. For example in the last step you can refer to a column in the first step that you already deleted a few steps ago:

= Table.AddColumn(#"Add IndexColumn 4th value", "Description from Sourcee", each Source{ [Index] } [Days difference] )

Check my examples:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLRNzTQNzIwMoSwjWFsBaVYHWRFpkiKLGBs25TEktSUzLQ0Dbiogq4C3BhNZDNM9Q1h2oBMI0wj4KIQIyywGmGOMMMIbl5xYm6qQk5+emayQmKxQmJSflkqTJspUNoYyTokVxDQZYKiy5hIXUBnIVllQLQuCyRdZnh1xQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, #"start date" = _t, #"due date" = _t, #"Days difference" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", Int64.Type}, {"start date", type text}, {"due date", type text}, {"Days difference", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Add Value Last Row" = Table.AddColumn(#"Added Index", "due date last row", each if [Index] > 0 then #"Added Index"{ [Index] -1 }[due date] else 0),
    #"Add IndexColumn 4th value" = Table.AddColumn(#"Add Value Last Row", "IndexColumn 4th value", each #"Add Value Last Row"{ 4 } [Index] ),
    #"Added Description from Source" = Table.AddColumn(#"Add IndexColumn 4th value", "Description from Sourcee", each Source{ [Index] } [Days difference] )
in
    #"Added Description from Source"

It's pretty great, isn't it?

If you have more questions just let me know.

Best regards

Denis