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

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
Super User

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
Super User

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 303 members 3,040 guests
Please welcome our newest community members: