I want to create a variable IsMonthEnd in calendar table which indicates 1 if its a month end date and 0 otherwise. Below is my calendar code. I have tried several ways but it produces all 0 in the variable IsMonthEnd.
Let EndDate = num(Date(Today(),'MM/DD/YYYY'));
Let StartDate = num(makedate(2008,1,1));
Let CurrentDate=num(date(Today(),'MM/DD/YYYY'));
Let vCurrentYear=Year(Today());
Let LY_YTD=num(AddMonths(Today(),-12,0));
Let DaysToEnd=Monthend(Today())-Today();
Let Timespan=Year(Today())-Year(Date($(StartDate)));
Let ToDate = Num(Today());
Let FromDate = Num(Today()-1);
Let QToDate = Ceil(Month(DATE(today()))/3);
Calender:
Load
*,
Date as Datex,
Year(Date) AS ,
Month(Date) AS ,
num(Month(Date)) AS [MonthNum],
num(Week(Date),00) AS [Week],
Day(Date) AS [Day],
WeekDay(Date) AS [WeekDay],
Year(Date)&num(Month(Date),00) AS [Year Month2],
if(Date<=$(CurrentDate),Date) AS Date2,// Dates until Current date, used for balance dates
Date(Monthstart(Date),'MMM-YY') AS [RollMonth],
Year(Date)&num(Month(Date),00) AS [Year Month],
Dual(WeekYear(Date)&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) AS [Year Week2],
Year(Date)&num(Week(Date),00) AS [Year Week],
Dual('T'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS ,
Year(Date)&Dual('Q'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS [Year Quarter],
Dual(Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) AS Quarter,
WeekEnd(Date)-1 as WeekEnd, //date -1 for week to end on Saturday
if(Date = MonthEnd(Date), 1,0) As IsMonthEnd
;
Load
Date($(StartDate)+(Iterno()-1),'MM/DD/YYYY') AS Date
// Date, to be used as key feild to transactions
Autogenerate 1
While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate))
;
What am i doing wrong?
I have daily observations. My objective is to plot only last day of month data in a line chart. Once i get this variable then i can use set analysis to create an expression. Also suggest if there is a better way to plot month end data.
