Getting Value at Previous Month and so on

[skumari]

Dear Team,

I have below dataset, I want to get columns as Previous mOnth, Current Month, Previous Month-2 with the value that is appearing when the respective date is there, given desired output. Please note current month in the dataset is the max month, which is June right now

Category        Date                   Amount

 Chairs         12/06/2019            300

 Tables        13/05/2019            310

 Tables         17/04/2019            211

   Tables         20/06/2019           453

Desired Output

Category       CM       PM        PM-1        PM-2

Chairs            300      0             0               0

Tables            453       310         211           0

is it possible, below formula does not work? (Pr

IF [Date] = DateTimeAdd([Date],-1,'month') then [Amount]

else 0 endif

[Ladarthure · Accepted solution]

hi @skumari, 

here is a way to do it, you have to get the max date first or have a file with the maximum date, then you append it to have a new column and then you calculate which month it is. (solution attached in the workflow)

Hope it helped

[JosephSerpis]

Hi @skumari  Here an example I mocked up 

[danilang]

Hi @skumari 

Both @JosephSerpis's and @Ladarthure's are close, but they don't get exactly the output you're looking

This one expands on the previous ones by ensuring that all periods are represented in the output and the column names are correct

Dan

[skumari]

Thanks, everyone, actually all the solutions worked, but I picked from @Ladarthur', fewer steps.