How to Create Monthly Loop which ends at today's date

[rohitk1609]

Hi techies,

I have an use case where I have two dates vMinDate which is lets say 1 Jan 2018 and vMaxDate which is today's date 8 Dec 2018. I want to create a monthly loop based on 30 days duration:

let vMinDate = 1 Jan 2018

let vMaxDate  = 8 Jan 2020

Table should look like

Year     Date           MonthRunningNo 

2018   1 Jan 2018            1

2018   2 Jan 2018            1

..

..

2018   30 Jan 2018         1

2018   31 Jan 2018         2

2018   1 Feb 2018           2

....

....

2020   8 Dec 2020          X

lets say by 30 days duration 8 days left to 8 Dec 2020, so last step in loop would have 8 days for month running number.

30 days should come in one step of monthly loop. 

Thanks in advance

Rohit

[Lisa_P]

let vMinDate = Num(Date#('1 Jan 2018', 'D MMM YYYY'));
let vMaxDate = Num(Date#('8 Jan 2020', 'D MMM YYYY'));

Dates:
Load Year(Date) as Year,
Date,
(Year(Date)-Year($(vMinDate)))*12 + Num(Month(Date)) as MonthRunningNo;
LOAD
Date( StartDate + IterNo() - 1 ) as Date
While StartDate + IterNo() - 1 <= EndDate;
LOAD * INLINE
[StartDate, EndDate
$(vMinDate), $(vMaxDate)
];