Auto Generate Calendar

[Anonymous]

Hi All,

I need some help from community. Assume I have the following SourceDate with MMM-YY format:

SourceDate

Oct-15

Sep-15

Aug-15

Jul-15

Jun-15

May-15

Apr-15

Mar-15

Feb-15

Jan-15

Dec-14

And now I need to auto generate a ReportDate field in order to get previous month year until the current month year in the table. The following table is the result which is I want.

SourceDate	ReportDate
Oct-15	Oct-15
Oct-15	Sep-15
Oct-15	Aug-15
Oct-15	Jul-15
Oct-15	Jun-15
Oct-15	May-15
Oct-15	Apr-15
Oct-15	Mar-15
Oct-15	Feb-15
Oct-15	Jan-15
Sep-15	Sep-15
Sep-15	Aug-15
Sep-15	Jul-15
Sep-15	Jun-15
Sep-15	May-15
Sep-15	Apr-15
Sep-15	Mar-15
Sep-15	Feb-15
Sep-15	Jan-15
Aug-15	Aug-15
Aug-15	Jul-15
Aug-15	Jun-15
Aug-15	May-15
Aug-15	Apr-15
Aug-15	Mar-15
Aug-15	Feb-15
Aug-15	Jan-15
Jul-15	Jul-15
Jul-15	Jun-15
Jul-15	May-15
Jul-15	Apr-15
Jul-15	Mar-15
Jul-15	Feb-15
Jul-15	Jan-15
Jun-15	Jun-15
Jun-15	May-15
Jun-15	Apr-15
Jun-15	Mar-15
Jun-15	Feb-15
Jun-15	Jan-15
May-15	May-15
May-15	Apr-15
May-15	Mar-15
May-15	Feb-15
May-15	Jan-15
Apr-15	Apr-15
Apr-15	Mar-15
Apr-15	Feb-15
Apr-15	Jan-15
Mar-15	Mar-15
Mar-15	Feb-15
Mar-15	Jan-15
Feb-15	Feb-15
Feb-15	Jan-15
Jan-15	Jan-15
Dec-14	Dec-14
Dec-14	Nov-14
Dec-14	Oct-14
Dec-14	Sep-14
Dec-14	Aug-14
Dec-14	Jul-14
Dec-14	Jun-14
Dec-14	May-14
Dec-14	Apr-14
Dec-14	Mar-14
Dec-14	Feb-14
Dec-14	Jan-14

Anyone know anyway to doing it? I am very appreciate for your help.

Thanks.

Best Regards.

[Digvijay_Singh · Accepted solution]

Try this -

T1:

Load Date(Date#(SourceDate,'MMM-YY'),'MMM-YY') as SourceDate,

  Month(SourceDate) as Month

inline [

SourceDate

Oct-15

Sep-15

Aug-15

Jul-15

Jun-15

May-15

Apr-15

Mar-15

Feb-15

Jan-15

Dec-14 ];

Final:

Load Date(Date#(SourceDate,'MMM-YY'),'MMM-YY') as SourceDate,

  Date(AddMonths(SourceDate,-Iterno()),'MMM-YY') as ReportDate

Resident T1 

While Iterno()<=Month(SourceDate)-1;

DROP Table T1;

[marcus_sommer]

For generating calendars have a look here: How to use - Master-Calendar and Date-Values and I think this will be helpful, too: The As-Of Table.

- Marcus