Subtraction of date from the previous one

[GuilhermeDias]

I need to make a subtraction between dates, selecting the previous one from another column. I tried using the EARLIER function, but without success.

Returns the following error:

EARLIER/EARLIEST references a previous line context that does not exist.

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"EPI", type text}, {"Data Entrega", type date}, {"Data de troca", type date}})
in
    #"Tipo Alterado"

[StrategicSavvy]

Hi @GuilhermeDias 

You can try using this code for power query that calculate Lag of column using indexing. 

Here are the steps applied:

1. added index column

2. calculate lag using index

3. replace errors with null because first value cannot be calculated

4 changed format to date (using locale spanish)

5 subtracting one date from another in new column 'subtraction'

6 removing uneccesary columns

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    AddedIndex = Table.AddIndexColumn(Fonte, "Index", 1, 1, Int64.Type),
    CalculateLag = Table.AddColumn(AddedIndex, "Lag", each AddedIndex{[Index]-2}[Data de troca]),
    #"Replaced Errors" = Table.ReplaceErrorValues(CalculateLag, {{"Lag", null}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Errors", {{"Data Entrega", type date}, {"Data de troca", type date}, {"Lag", type date}}, "es-ES"),
    Subtraction = Table.AddColumn(#"Changed Type with Locale", "Subtraction", each [Data Entrega] - [Lag]),
    #"Changed Type" = Table.TransformColumnTypes(Subtraction,{{"Subtraction", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"EPI", "Data Entrega", "Data de troca", "Subtraction"})
in
    #"Removed Other Columns"

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn if you would like to connect: www.linkedin.com/in/lukasz-kozdron 

[GuilhermeDias]

Thank you very much for your help, but I would like the calculation to only consider within the same ID.

[StrategicSavvy · Accepted solution]

Yes , sorry for the oversight. There you go:

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0ktyS9SKMksyFdIzs9LzkhU0lEyMNQHIiMDQwtkjiWQY6gUq4NTm7GBviFcpZElhGNkQEgbzAIjQ2SOEdHajJA5xgQdaahvaATTBucQ1AaywAyk0gSZY0q+bUbk2QbUFgsA", BinaryEncoding.Base64), Compression.Deflate)),
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    AddedIndex = Table.AddIndexColumn(Fonte, "Index", 1, 1, Int64.Type),
    CalculateLag = Table.AddColumn(AddedIndex, "Lag", each
        if [Index] > 1 and [Empregado] = AddedIndex{[Index]-2}[Empregado] then
            AddedIndex{[Index]-2}[Data de troca]
        else null),
    #"Changed Type with Locale" = Table.TransformColumnTypes(CalculateLag, {{"Data Entrega", type date}, {"Data de troca", type date}, {"Lag", type date}}, "es-ES"),
    Subtraction = Table.AddColumn(#"Changed Type with Locale", "Subtraction", each [Data Entrega] - [Lag]),
    #"Changed Type" = Table.TransformColumnTypes(Subtraction, {{"Subtraction", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"EPI", "Data Entrega", "Data de troca", "Empregado", "Subtraction"})
in
    #"Removed Other Columns"

I also kept original Index in the M code.

[GuilhermeDias]

Hello @StrategicSavvy , sorry to bother you again. But how would I insert this same rule into the grouped table?

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    #"Linhas Classificadas" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
    #"Linhas Agrupadas" = Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})
in
    #"Linhas Agrupadas"

[StrategicSavvy]

Hi @GuilhermeDias 

I assumed you would like to sum the results. by employeer and EPI code. It required extra steps with sorting as well.  There you go :

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    #"Sorted Rows" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    CalculateLag = Table.AddColumn(AddedIndex, "Lag", each
        if [Index] > 1 and [Empregado] = AddedIndex{[Index]-2}[Empregado] then
            AddedIndex{[Index]-2}[Data de troca]
        else null),
    #"Linhas Classificadas" = Table.Sort(CalculateLag,{{"Empregado", Order.Ascending}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Linhas Classificadas", {{"Data Entrega", type date}, {"Lag", type date}}, "es-ES"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each [Data Entrega] - [Lag]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Subtraction"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"COD EPI", "Empregado"}, {{"Contagem", each List.Sum([Subtraction]), type nullable number}})
in
    #"Grouped Rows"

[GuilhermeDias]

My question is how to do "AddedIndex{[Index]-2}[Exchange Date]" within Table.Group

May be in the next step:

Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZFNCsIwEEavUrIuZGaiYg8huC9dlCDoxkjMhTyLFzNtaQm0kx8qZPOFeXzDvLYVCCBqcbXG3ZyxlXu8TKXNU997/w0o/SPAcxgaH1B0dQJWIHGZp2YKBHnwXEYYBiqEKQwqc22USDO8hEx4KDsN84cwHPc2057mCaYRvnw/b93bHLcrIOWTbeAc8gDjbb1SwtVmQ8xPQUPkrH/yoEo9MADvIQWsPHArsVeKNGxfyQPdDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COD EPI" = _t, EPI = _t, #"Data Entrega" = _t, #"Data de troca" = _t, Empregado = _t]),
    #"Linhas Classificadas" = Table.Sort(Fonte,{{"Empregado", Order.Ascending}}),
    #"Linhas Agrupadas" = Table.Group(#"Linhas Classificadas", {"COD EPI", "Empregado"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1)}})
in
    #"Linhas Agrupadas"

[GuilhermeDias]

able to achieve the expected result, thank you very much @StrategicSavvy 

[ThxAlot]