Date Derivations For Reference

[jerome_rajan]

Dear All,

I've formulated a few derivations to calculate certain dates (Listed in BOLD). If your time dimension cannot help or you don't have a time dimension, the following might help. Feel free to add or optimize my derivations. This thread can be a handy reference for many.

Last Day of 2nd Last Month

Code: Select all

DateOffsetByDays(YearFromDate(DateFromDaysSince(-1,Left(BUSINESS_DATE,7):"-01")):"-":Right("0":MonthFromDate(DateFromDaysSince(-1,Left(BUSINESS_DATE,7):"-01")),2):"-01",-1)

Last Day Of Last Month

Code: Select all

DateOffsetByComponents(YearFromDate(BUSINESS_DATE):"-":Right("0":MonthFromDate(BUSINESS_DATE),2):"-01",0,0,-1)

Last Day Of 2nd Last Quarter

Code: Select all

DateOffsetByDays(YearForQtr:"-": Right("0":If ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1)<0 Then ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1)+ 12 Else ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1),2) :"-01",-1)

where

Code: Select all

YearforQtr=If AsInteger((MonthFromDate(BUSINESS_DATE)+2)/3)=1 Then YearFromDate(BUSINESS_DATE)-1 Else YearFromDate(BUSINESS_DATE)

Last Day Of Last Quarter

Code: Select all

DateOffsetByDays(YearFromDate(BUSINESS_DATE):"-": Right("0":If ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1)<0 Then ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1)+ 12 Else ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1),2) :"-01",-1)

Last Day Before 12 Months

Code: Select all

DateOffsetByDays(YearFromDate(DateOffsetByComponents(BUSINESS_DATE,0,-12,0)):"-":Right("0":MonthFromDate(DateOffsetByComponents(BUSINESS_DATE,0,-12,0)),2):"-01",-1)

Last Day Of Last Financial Year

Code: Select all

 If (MonthFromDate(BUSINESS_DATE)>=4 And MonthFromDate(BUSINESS_DATE)<=12) Then (DateOffsetByDays(StringToDate(YearFromDate(BUSINESS_DATE):"-04-01"),-1)) Else (DateOffsetByComponents(StringToDate(YearFromDate(BUSINESS_DATE):"-04-01"),-1,0,-1))

[ray.wurlod]

Few places in the world have a financial year ending on April Fools' Day.

There's much more you can do with these functions. For example:
MonthFirst (first day of current month) - three different ways:

Code: Select all

DateOffsetByDays(TheDate, -1 * MonthDayFromDate(TheDate))

DateOffsetByComponents(TheDate, 0, 0, -1 * MonthDayFromDate(TheDate))

DateFromDaysSince(-1 * MonthDayFromDate(TheDate), TheDate)

The trick is to experiment. Notice that none of the above examples had to resort to converting data type from date.

Even better is to have a date dimension table with all these kinds of dates pre-evaluated.