Dynamic Select Prior 12 Months

[jsamstad]

Hello! Is there a way to incorporate a date table so that I can dynamically select the prior twelve periods given the today is in the most recent period (202403) ? The table is cut short given the table limitation in the message, but imagine the columns go past 202307.

Thanks in advance!

Class ID	202403	202402	202401	202312	202311	202310	202309	202308	202307
123	10	5	15	10	5	20	10	5	20
456	5	10	5	0	5	30	10	10	10

[BrandonB · Accepted solution]

I like to transpose, parse out the month, and use a Sample tool for the first 12 records grouping by the ID column, and then crosstab back. Workflow is attached, but I think this is a relatively elegant solution without many tools. Technically you don't even need the parse out the month, but I did so just in case you needed to do additional adjustments. For example, you could use a sort tool after the formula to ensure that the months are in ascending order just in case your columns aren't ordered as they are now. 

[jsamstad]

Nice! I adapted it as there are periods in front of the current period which I failed to mention. Thank you!

[BrandonB]

Awesome glad to help!

[SPetrie · Accepted solution]

A dynamic select tool with formulas may also work for you.

DateTimeParse([Name]+"01","%Y%m%d") >= datetimeadd(DateTimeFirstOfMonth(),-12,"months") or [Name]="Class ID"

[jsamstad · Accepted solution]

That is slick!