Difrential production Special case

[OscarSuarez10]

Hello, I need to calculate the differential production per Well and Year using power query, there is a special case when at the last year only have one date of production so I have to use the previous year last production, can you help me to do that? This What I mean: 

Special case:  1200 (feb-21) - 1000 (april - 20 ) = 200

Year	Well	Production	Diferential Production Per Year Well A
jan-19	A	100		2019	200
feb-19	A	200		2020	550
mar-19	A	250		2021	200
april-19	A	300
jan-20	A	450	Diferential Production Per Year Well B
fen-20	A	600		2019	300
mar-20	A	7000		2020	300
april-20	A	1000		Special case 2021	200
feb-21	A	1700
mar-21	A	1900
jan-19	B	300
feb-19	B	400
mar-19	B	500
april-19	B	600
jan-20	B	700
fen-20	B	850
mar-20	B	950
april-20	B	1000
feb-21	B	1200

[Nolock · Accepted solution]

Hi @OscarSuarez10,

I have used the same code as in your last question, just modified it a little bit. I've also used sample data from your last post.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDEB/ZeS8QtMNxKPBD/DmoexQZ8VJ3aTM/7dUx8K2eCm0j+blWQtHN3oo4RSH67sdu6FPl7MPAZrSwsP1CpXRuE+vqHU6Dxnc/GUGhoGni+1dFaiKH6wYdK/YhflfzdBkMnoxcDJlUAmmDOtNU0a7rYH4bVrvwGpJqqV/tSTXklRLUu3axGqXJlZLYm0izQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [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 Well
    #"Grouped Rows by Well" = Table.Group(Year, {"Well"}, {{"AllRowsOfWell", each _, type table [Date=date, Production=number, Well=text, Year=number]}}),  
    // group by year in every well
    TransformColumns = Table.AddColumn(
        #"Grouped Rows by Well", 
        "Grouped Rows By Year",
        each 
            let
                // group by Year and get min and max of production per year
                #"Grouped Rows" = Table.Group([AllRowsOfWell], {"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
                #"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"
        
    ),
    #"Expanded Grouped Rows By Year" = Table.ExpandTableColumn(TransformColumns, "Grouped Rows By Year", {"Year", "Difference"}, {"Year", "Difference"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows By Year",{"AllRowsOfWell"})
in
    #"Removed Columns"

[OscarSuarez10]

Thank You @Nolock  it worked Perfectly!

[OscarSuarez10]

Hello @Nolock  can you help me with is one? Thank You