Month Matching and Sequencing

[SH_94]

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

[Raj · Accepted solution]

Use multi-row formula 

workflow attached for reference

mark as solution if it solves your problem

[binuacs]

@SH_94 One way of doing this

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

[OllieClarke]

@SH_94 here's an edited version of @binuacs'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