Dynamic Select Prior 12 Months

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

Period Example.xlsx

Date Time

Transformation

Accepted answers

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.

Dynamic selection.png

Dynamic Selection.yxmd

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"

dynamic select.PNG

That is slick!

All comments

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.

Dynamic selection.png

Dynamic Selection.yxmd

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

Awesome glad to help!

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"

dynamic select.PNG

That is slick!

Quick Links

Tags

This months top contributors

AkimasaKajitani 390

michael_weaver 335