EOMonth Function

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

EOMonth Function

Post by manu0521 »

Hi,
Is there an EOMonth function available in tm1 ti . If not how do I achieve it to get the end of month date based on a given date .

I would need to get the end of month the current month and next month as well ,
Any suggestions is appreciated .

Thanks,
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: EOMonth Function

Post by paulsimon »

Hi

You can use pretty much any of the Rule Functions in TI. Look at the Date and Time Rules Functions section of the TM1 Reference Guide.

DAYNO will get you the Date Serial Number of a string date which should be in the form YYYY-MM-DD

So if you have a date in string format such as '15-06-2019' then use SUBST to split it up

vMM = StringToNumber( subst(vDate,4,2) ) ;
vYYYY = StringToNumber( subst( vDate, 6, 4 ) ) ;

# We now need to get the first of the following month

vNextMM = vMM + 1 ;

IF( vNextMM > 12 ) ;
vNextYYYY = vYYYY + 1 ;
ELSE ;
vNextYYYY = vYYYY ;
ENDIF ;

vFirstNextMonth = NumberToString( vNextYYYY ) | '-' | vNumberToString( vNextMM ) | '-01' ;

# Subtract 1 day from the Date Serial of this to get a Date in TM1 Date Serial Format that is the end of this month
# This approach gets past the issue of months having 28, 29, 30, or 31 days.

vEOMonth = DAYNO( vFirstNextMonth ) - 1 ;

There may be a few issues in the syntax above since I haven't tried it, and this is from memory, but it should be pretty close.

Regards

Paul Simon
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: EOMonth Function

Post by manu0521 »

Thanks , will give it a try .
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: EOMonth Function

Post by manu0521 »

Hi thanks,

I was able to get this working using the below approach .
Post Reply