Create a Calendar of just month end dates

[Anonymous]

I'm working on a calendar of just month end dates. The problem is that the result is a list of month end dates that are duplicated.

The code looks like this.

Let vStartDate = Date(AddMonths(YearStart(today()), -60));
Let vEndDate = Date(MonthEnd(Today()));
LET vNumberOfDays = vEndDate - vStartDate + 1; 

Let MonthDiff = Num(((year(vEndDate) * 12) + month(vEndDate)) - (((year(vStartDate) * 12) + month(vStartDate))) + 1);
LET vNumberOfMonths =   MonthDiff + 1; 

FiscalCalendar: 

LOAD 

*, 

Month(FiscalDate) AS Month, 

Year(FiscalDate) AS Year, 

'Q' & Ceil(Month(FiscalDate)/3) AS Quarter, 

MonthName(FiscalDate) AS MonthYear, 

'Q' & Ceil(Month(FiscalDate)/3) & '-' & Year(FiscalDate) AS QuarterYear; 

LOAD Date as FiscalDate;

LOAD 

Date(MonthEnd(Date(Date(AddMonths(YearStart(today()), -60))+recno()-1))) as Date

AutoGenerate $(vNumberOfMonths); 

The results look like this, What am I doing wrong?

[petter]

If you have a closer look at the second to last line of your load script you will find that you have misplaced the +recno()-1 and you can also get rid of the two Date() functions you are using as they serve no purpose. This should work as your last three lines of your load script:

LOAD

  MonthEnd(AddMonths(YearStart(today()),-60+RecNo()-1)) as Date

AutoGenerate 12;

[Anonymous]

Peter,

Sorry, that doesn't quite work.

it's not just a years worth of month end it also includes the year, so I go back a little over 5 years, I want to keep the number of months as is. I updated with the following

MonthEnd(AddMonths(YearStart(today()),-60+RecNo()-1)) as Date

AutoGenerate $(vNumberOfMonths); 

I have to have the date after MonthEnd otherwise I keep ending up with this. as my Fiscal Date.

[Anonymous]

I was able to correct the issue and get the calendar I want by adding the Date() function in front of Month end date.

Now I get what I need.  I renamed these fields as valuation date, etc. in order to match my fact table.  That works and the model looks correct.  However, when I try to use the valuation date and valuation month year fields I get odd results.  See below First off you will notice i'm getting 2 of every valuation date, even though I only have one in my qvd calendar file.  Why is that happening?

The other is that the 2 aren't relating correctly.  If I choose 3/31/2013 my data changes but the valuation month year gets nulled out.

Similar happens when I choose March 2013, everything gets 0'd out.  Why would that be?

[petter]

The 12 was just a placeholder for your variable ... The logic will work for the last 5 years if you replace it with 60. Misleading of me to not do that in the first place - it was my "test"/dummy code that I forgot to thoroughly go through before I posted it...

[Anonymous]

Thanks, Petter, did get a chance to look at my issue below? i'm not sure what i'm doing wrong?