Month Matching and Sequencing

Hi Community,

I have the input data as per screenshot below and desired output. May i know how can i build the workflow for this scenario?

For the desired output , on the left hand side, it is represent current yearmonth while on the right hand , it is represent the following yearmonth or next month. For example, if the current yearmonth is 202112 , then the following yearmonth will be 202201 with the amount usd copied as well.

I noticed that there are certain yearmonth missing on the left handside data, For example, for the product CC, it is missing the yearmonth 202202, Hence, i would like to include those missing year month but the USD amount value will be empty.

Many thnaks for the support

Month Matching.xlsx

Common Use Cases

Accepted answers

Raj

Use multi-row formula

workflow attached for reference

mark as solution if it solves your problem

sol multi row formula.yxzp

All comments

Raj

Use multi-row formula

workflow attached for reference

mark as solution if it solves your problem

sol multi row formula.yxzp

binu_acs

@SH_94 One way of doing this

Multi_Row_012323.yxmd

Christina_H

See attached. There are two steps to this, first filling in the missing rows, then matching to the data from the following month. I've converted the numbers into dates to make them easier to work with.

Month matching.yxmd

OllieClarke

@SH_94 here's an edited version of @binu_acs's solution which aligns with your desired output. You only need the multi-row for the Amount, as we can use a normal formula for the [YearMonth 1]

Datetimeformat(
datetimeadd(
datetimeparse([YearMonth],'%Y%m')
,1,'month')
,'%Y%m')

This approach means you don't get the null [YearMonth 1] values

Ollie

YearMonth.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703