Continue year-month column

[egor]

Hi. After running timeseries forecasting, I have a dataset like shown on the pic. I need to continue "year-month" column by putting in the red area 2016-01, 2016-02, etc. Could you please help with ideas how to do it quickly? Thanks a lot!

[patrick_digan]

@egor It's not easy on the eyes, but this seems to work in a multi-row formula tool that updates year-month:

IIF(isnull([Year-Month]),DateTimeAdd(datetimeparse([Row-1:Year-Month]+"-01","%y-%m-%d"),1,"months"),[Year-Month])

[SeanAdams · Accepted solution]

:-) I loved your comment @patrick_digan about being easy on the eyes.

I think we've had the same idea:

- 2015-12 is actually almost a date

- So - why not treat this as a date and add a month ?

The multi-row formula that @patrick_digan then does the following:

- Add '01' to the end to make this a full date (e.g. 2015-12-01)

- Then parse it into a date

- then add 1 month using "DateAdd"

- then truncate the '01' from the end.

You can also do this by exploding this out into 3 controls:

- creating a new column for the date-version of this year-month column using a standard formula.    This essentially takes step 1 & 2 of the above.

      - This gets you to a place where you have the Year-month column and Year_month_as_date column completed for first few rows

- then do a multi-row formula to do a date-add of 1 month to each row where the [year_month_as_date] is null but the one on previous row is non-null

      - now you have a table with the year_month_as_date fully populated for every row

- Finally finish with a formula tool to back-populate the year_month column where it's null using the [year_month_as_date] column

The second version does the same as the first, it's just exploded out a little to show the workings in the middle (in case you want to understand this a little better).  

Cheers @egor

Sean

[egor]

@patrick_digan thank you very much! Unfortunately, it didn't work for me. Maybe I put some of tickmarks or some other detail in tool menu incorrectly.

What happened is that these later months went to the beginning of the dataset, but still without month info... Strange =)

[egor]

@SeanAdams thank you very much! This solution worked. For the sake of people who might require it in future, this is how I implemented it.