Add calculated item in pivot table from different items in the same field

[MGadAllah]

I tried to add a calculated item in pivot table from different items in the same field and the items belong to different sections or parts, but all used items belong to the same field or same column, but always give wrong result.
What is the right way to do so?

 

[jdellasala]

If the source is Power Query, yes. You'd add an Index column, and then add a custom column with the value of the source column referencing the row by the Index number.

let
    Source = Excel.CurrentWorkbook(){[Name="GDPC1"]}[Content],
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    AddedIndex = Table.AddIndexColumn(PromotedHeaders, "Index", 0, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "Previous Quarter", each if [Index]>0 then AddedIndex[value]{[Index]-1} else null)
in
    AddedCustom

End up with this:

Just change the offset (-1 in the [Index]-1 part of the M code) if you need to go further back, or a plus value if you need to go ahead. Note the code above prevents the error that would occur for row 1 by not executing if [Index]=0. Adjust to prevent errors or just replace the errors that can be expected with null.

Hope that helps.