Passing Previous month value to a new column

[kiotsuresh]

I have the Data like below,

Date	Sales	Profit
05/01/2022	100	300
04/01/2022	200	400

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

I need the below Output:

Date	Sales	SalesPM(Prev month)	Profit	ProfitPM(Prev month)
05/01/2022	100	400(April month sales)	300	400 (April month Pft)
04/01/2022	200	(March month Sales)	400	(March month Pft)

Can somebody help me in achieving this?

[AngelosPachis]

Hi @kiotsuresh 

Welcome to the community. Please use the tags sparingly to get to the answer you are looking for faster. You will probably need to have a look at the multi-row formula tool. That will help you populate the profit and sales for a certain month from the row below (i.e. from the previous month).

Hope that helps,

Angelos

[Emmanuel_G]

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 !

[TonyaS]

@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. 

[kiotsuresh]

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

Date in MM/DD/YYYY format

Date

Datasource

Category Group

Category type

category Segment

Category type lvl1

category type lvl2

category type lvl3

manu Year classification

Profit

Profit PM(new column)

Sales

SalesPM(New Column)

Revenue

Revenue PM(New Column)

1/1/2020

SQL

Furniture

Table

Amazon

Join

Type 1

Type A

Year 2015

100

December 2019 profit

100

December 2019 Sales

100

December 2019 Revenue

1/1/2020

SQL

Furniture

Chair

Wallmart

Join

Type 1

Type A

Year 2015

200

December 2019 profit

200

December 2019 Sales

200

December 2019 Revenue

1/1/2020

SQL

Furniture

Bookcase

Ebay

Join

Type 1

Type A

Year 2015

300

December 2019 profit

300

December 2019 Sales

300

December 2019 Revenue

1/1/2020

SQL

Furniture

Dining

Flipkart

Join

Type 1

Type A

Year 2015

400

December 2019 profit

400

December 2019 Sales

400

December 2019 Revenue

2/1/2020

SQL

Furniture

Table

Amazon

Union

Type 2

Type C

Year 2016

500

100

500

100

500

100

2/1/2020

SQL

Furniture

Chair

Wallmart

Union

Type 2

Type C

Year 2016

600

200

600

200

600

200

2/1/2020

SQL

Furniture

Bookcase

Ebay

Union

Type 2

Type C

Year 2016

700

300

700

300

700

300

2/1/2020

SQL

Furniture

Dining

Flipkart

Union

Type 2

Type C

Year 2016

800

400

800

400

800

400

3/1/2020

SQL

Furniture

Table

Amazon

Blend

Type 3

Type D

Year 2017

900

500

900

500

900

500

3/1/2020

SQL

Furniture

Chair

Wallmart

Blend

Type 3

Type D

Year 2017

1000

600

1000

600

1000

600

3/1/2020

SQL

Furniture

Bookcase

Ebay

Blend

Type 3

Type D

Year 2017

1100

700

1100

700

1100

700

3/1/2020

SQL

Furniture

Dining

Flipkart

Blend

Type 3

Type D

Year 2017

1200

800

1200

800

1200

800