Forum Discussion
Substract Production from the last year
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 |
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"
1 Reply
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"