Passing Previous month value to a new column

Question

I have the Data like below,

DateSalesProfit05/01/202210030004/01/2022200400

Date is is (MM/DD/YYY-Format).

I need the below Output:

DateSalesSalesPM(Prev month)ProfitProfitPM(Prev month)05/01/2022100400(April month sales)300

400 (April month Pft)04/01/2022200      (March month Sales)400        (March month Pft)

Can somebody help me in achieving this?

kiotsuresh · Answer

Instead the Data template I posted above consider the data template below, the solution required is same.

Date in MM/DD/YYYY format

DateDatasourceCategory GroupCategory typecategory SegmentCategory type lvl1category type lvl2category type lvl3manu Year classificationProfitProfit PM(new column)SalesSalesPM(New Column)RevenueRevenue PM(New Column)1/1/2020SQLFurnitureTableAmazonJoinType 1Type AYear 2015100December 2019 profit100December 2019 Sales100December 2019 Revenue1/1/2020SQLFurnitureChairWallmartJoinType 1Type AYear 2015200December 2019 profit200December 2019 Sales200December 2019 Revenue1/1/2020SQLFurnitureBookcaseEbayJoinType 1Type AYear 2015300December 2019 profit300December 2019 Sales300December 2019 Revenue1/1/2020SQLFurnitureDiningFlipkartJoinType 1Type AYear 2015400December 2019 profit400December 2019 Sales400December 2019 Revenue2/1/2020SQLFurnitureTableAmazonUnionType 2Type CYear 20165001005001005001002/1/2020SQLFurnitureChairWallmartUnionType 2Type CYear 20166002006002006002002/1/2020SQLFurnitureBookcaseEbayUnionType 2Type CYear 20167003007003007003002/1/2020SQLFurnitureDiningFlipkartUnionType 2Type CYear 20168004008004008004003/1/2020SQLFurnitureTableAmazonBlendType 3Type DYear 20179005009005009005003/1/2020SQLFurnitureChairWallmartBlendType 3Type DYear 20171000600100060010006003/1/2020SQLFurnitureBookcaseEbayBlendType 3Type DYear 20171100700110070011007003/1/2020SQLFurnitureDiningFlipkartBlendType 3Type DYear 2017120080012008001200800

Tonya_Smith · Answer

@kiotsuresh

I see the AMP Engine tag used, but I do not see anything stating that this issue was specific to AMP Engine. Does anyone know if this was AMP specific? There are a lot of tags used on this post, but I try to follow anything with the AMP Engine tag.

Emmanuel_G · Answer

Hi @kiotsuresh ,

Hope you're fine.

Please find a test below with exact desired output.

Let us know if it works the way you want.

Cheers !

Sales and profit.yxmd