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
Solved! Go to 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
Hi @skumari Here an example I mocked up
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
Thanks, everyone, actually all the solutions worked, but I picked from @Ladarthur', fewer steps.