cancel
Showing results for
Did you mean:
Highlighted
Member

## Substract Production from the last year

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

(curRecord) =>
List.First(
Table.SelectRows(
SortedTable,
each Date.Year([TIME]) = curRecord[Year]
)[FOPT]
),
type number
),
(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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User II

## Re: Substract Production from the last year

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}}),
#"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}}),
// create column maxOfLastYear by reading the previous row
// calculate the difference
#"Added Difference" = Table.AddColumn(#"Added MaxOfLastYear", "Difference", each if [MinOfYear] = [MaxOfYear] then [MaxOfYear] - [MaxOfLastYear] else [MaxOfYear] - [MinOfYear])
in

Super User II

## Re: Substract Production from the last year

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}}),
#"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}}),
// create column maxOfLastYear by reading the previous row
// calculate the difference
#"Added Difference" = Table.AddColumn(#"Added MaxOfLastYear", "Difference", each if [MinOfYear] = [MaxOfYear] then [MaxOfYear] - [MaxOfLastYear] else [MaxOfYear] - [MinOfYear])
in

Announcements