Substract Production from the last year

[OscarSuarez10]

Hello @Nolock  I´m using the following code to calculate diferential productions per year:

#"MinOfYear" = Table.AddColumn(RenameColumn, "MinOfYear",
(curRecord) =>
List.First(
Table.SelectRows(
SortedTable,
each Date.Year([TIME]) = curRecord[Year]
)[FOPT]
),
type number
),
#"MaxOfYear" = Table.AddColumn(MinOfYear, "MaxfYear",
(curRecord) =>
List.Last(
Table.SelectRows(
SortedTable,
each Date.Year([TIME]) = curRecord[Year]
)[FOPT]
),
type number
),
#"Substract" = Table.AddColumn(MaxOfYear, "Diferencial aceite", each if [MaxfYear] = [MinOfYear] then [MaxfYear] else [MaxfYear] - [MinOfYear], type number)

When the last year only has one date I get the production of the year , but I need to substract the last production of the previous year like in the following table:

Year	Production		Diferential production 2019	300
jan-1-2019	100
feb-1-2019	200
march- 1- 2019	300		Diferential production 2020	500
april-1-2019	400
jan-1-2020	200
feb-1-2020	300		Diferential production 2021	300
march- 1- 2020	400
april-1-2020	700
feb-1-2021	1000

[Nolock · Accepted solution]

Hi @OscarSuarez10,

here we go.

At the begining I prepare some test data and create a column Year. The rest is commented in code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUQooyk8pTS7JzM9TitWJVspKzNM11DUyMLQEyhkaGIAF01KTEIJGUMHcxKLkDF0FQ10FqIQxVCKxoCgzB6HeBCoMM9nIAMkQmMlgQWMsJoMlTDBMBguboxtiCHEzUDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Production", Int64.Type}}),
    Year = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date])),
    
    // till now data preparation
    
    // group by Year and get min and max of production per year
    #"Grouped Rows" = Table.Group(Year, {"Year"}, {{"MinOfYear", each List.Min([Production]), type number}, {"MaxOfYear", each List.Max([Production]), type number}}),
    // add an index columns
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    // create column maxOfLastYear by reading the previous row
    #"Added MaxOfLastYear" = Table.AddColumn(#"Added Index", "MaxOfLastYear", each try #"Added Index"{[Index] -1}[MaxOfYear] otherwise 0, type number),
    // calculate the difference
    #"Added Difference" = Table.AddColumn(#"Added MaxOfLastYear", "Difference", each if [MinOfYear] = [MaxOfYear] then [MaxOfYear] - [MaxOfLastYear] else [MaxOfYear] - [MinOfYear])
in
    #"Added Difference"