cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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:

 

YearProduction Diferential production 2019300
jan-1-2019100 
feb-1-2019200   
march- 1- 2019300 Diferential production 2020500
april-1-2019400 
jan-1-2020200   
feb-1-2020300 Diferential production 2021300
march- 1- 2020400 
april-1-2020700   
feb-1-20211000  
1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Substract Production from the last year

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"

 Capture.PNG

View solution in original post

1 REPLY 1
Super User II
Super User II

Re: Substract Production from the last year

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"

 Capture.PNG

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors