Use of MONTHEND function prevents facts records from being selected via a calendar dimension

[#1]

I came across an interesting issue when I was loading some General Ledger data. I needed to construct a date from seperate fields (i.e. the year and month are stored in different fields). If I use the MONTHEND function within the LOAD script, the date field does not align to the date from the calendar dimension (i.e. selecting a month from the calendar dimension yields no fact records). However, as soon as I remove the MONTHEND function, the dates are aligned. See below:

Dates do align:

DATE

((MAKEDATE(year,APPLYMAP('Month_Map',num(time_key),-2),1)),'DD-MMM-YYYY')

Dates do not align:

DATE

(MONTHEND(MAKEDATE(year,APPLYMAP('Month_Map',num(time_key),-2),1)),'DD-MMM-YYYY')

[#2]

Also note that the calendar dimension contains ALL dates.

[hector]

Hi, be carefull with monthend(), because it includes some decimal values (the decimal representation of 23:59:59), maybe if you use floor(monthend(......)) or dayname(monthend(......)) works again

Rgds

[#4]

Agreed. I was troubleshooting this issue and set the format of the date to display as a number with decimal places. The decimal places were all zeroes.

I just tried the floor function in my load script and that still did not resolve the issue. I am going to leave this as the first of the month (since it is a monthly metric) and move on. Thanks for the response.

DATE

(FLOOR(MONTHEND(MAKEDATE(year,APPLYMAP('Month_Map',num(time_key),-2),1)),'DD-MMM-YYYY'

))