Try this master calendar script code
YearStart( Today() ) + IterNo() as Date,
Rand() as Value
While (YearStart(Today()) + IterNo()) <= YearEnd(Today());
Date(Date) as DateNew
Hi John, check this document: Master Calendar Generation Script
In this part of the script:
Min(InvoiceDate) AS MinDate,
Max(InvoiceDate) AS MaxDate
MinDate should store the start date of the calendar and MaxDate the end date, it can be dynamic absed on data like in the sample, you can fixed the dates using Date('YourDate') or you can create a variable that sets the min and max dates and use the variables to assign values to MinDate and MaxDate.
Hi John, you can create only one record per month changing from:
Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load Date(AddMonths(MinDate, IterNo() -1)) AS InvoiceDate While Date(AddMonths(MinDate, IterNo() -1)) <= Num(MaxDate);
Anyway, dont' worry too much for creating all dates, as it is another table you can create all dates without affecting your data. and if you load 5-6 years of dates there will be less than 2k extra records.