TM1 Forum

Hi,

We are building a lease accounting calculation in tm1 .

In this calculation we would like to replicate the functionality of excel's days360 function.

Days360 in excel would give me the number of days between two dates considering a 30 month period for each month .

I can do date differnce in tm1 but is anyone tried to achieve the same as days360 in tm1 .

Any suggestions is appreciated on how to achieve this is much appreciated.

Thanks,

Hello,

You started another topic which is very similar to this one:
https://www.tm1forum.com/viewtopic.php? ... 267#p72267

You received good feedback but then you did not close the topic or continue with it.
What is the status of the topic ? It might give you pointers to solve this topic too.

Jut replied to the old thread. That one was getting EO month in tm1 .

But this one looks little complex l,because the difference can be any no of years.

Thanks,

TM1 doesn’t have an out of the box function for this so you will need to create the logic yourself.

Rules or TI should be relatively simple. You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.
The bit where you need some “if” logic is on the partial months (first and last month) where the it handles what happens if there are not 30 days in that month. Should be fairly straight forward though.

As a starting point it would be beneficial to have a month dimension that has elements which are concatenation of month and year. Have attributes for the first and last day of each month in here.

The logic for the first month would be along the lines of:
If my start date is greater than 29 then use 1 day otherwise use 30 minus my start day.
For the last month something like:
If the end date is 31 then use 30 otherwise just use the day number.

HI ,

Thanks for the input .

You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.

How do you workout the number of full months between jan 10 2016 to jan 9 2021 .

Should i start the difference between feb 1 2016 and dec 31 2021.

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 amShould i start the difference between feb 1 2016 and dec 31 2021.

Yes. You could make use of a simple dimension containing concatenated years and months as n-level elements.
If you make sure that the elements are all in chronological order then a simple "DIMIX( dim, last month ) - DIMIX( dim, first month )" will be the heart of the solution.

Other than this, yes there will be some more date manipulations left. It isn't the most enjoyable part of TM1, IMHO, but should be feasible anyway.

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 am How do you workout the number of full months between jan 10 2016 to jan 9 2021 .

Divide the number of days between jan 10 2016 and jan 9 2021 by 30.4 and truncate the remainder. That integer amount will be the number of full months. This will work as long as the number of years between the two dates is no more than 67.

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 am
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .

hi,

something like that should work.

Date1 = jan 10 2016
Date2 = jan 9 2021
1: full month of the first year 12-Month(Date1) = 11
2: month of the full years: (Year(Date2)- (Year(Date1)+1) * 12 = 48
3: Full moth of last year: Month(Date2)-1 =0
4: now it depends what's a full month: if( day(Date1)-1 = day(date2) OR day(date1)> day(Date2)), 1,0)

Days360 in Excel gives you 59.96666667
so you need to decide if this are 59 month (step 1 to 3) or 60 (step1 to 4)

best regards,
orlando

tomok wrote: ↑Wed Apr 17, 2019 1:37 pm

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 am How do you workout the number of full months between jan 10 2016 to jan 9 2021 .

Divide the number of days between jan 10 2016 and jan 9 2021 by 30.4 and truncate the remainder. That integer amount will be the number of full months. This will work as long as the number of years between the two dates is no more than 67.

Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.

Wim Gielis wrote: ↑Wed Apr 17, 2019 1:00 pm

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 amShould i start the difference between feb 1 2016 and dec 31 2021.

Yes. You could make use of a simple dimension containing concatenated years and months as n-level elements.
If you make sure that the elements are all in chronological order then a simple "DIMIX( dim, last month ) - DIMIX( dim, first month )" will be the heart of the solution.

Other than this, yes there will be some more date manipulations left. It isn't the most enjoyable part of TM1, IMHO, but should be feasible anyway.

so doy store an attribute value for each element with month number ?

I like this approach, so its like
200001 1
200012 12
201609 500
202109 750

etc..

An attribute could be a solution and not too bad, but the Dimix function gives you the index number of an element in a dimension.
So if the elements are added in the correct sequence and they remain like that, a simple difference of 2 DIMIX'es is sufficient.

manu0521 wrote: ↑Wed Apr 17, 2019 6:00 pm

tomok wrote: ↑Wed Apr 17, 2019 1:37 pm

manu0521 wrote: ↑Wed Apr 17, 2019 11:53 am How do you workout the number of full months between jan 10 2016 to jan 9 2021 .

Divide the number of days between jan 10 2016 and jan 9 2021 by 30.4 and truncate the remainder. That integer amount will be the number of full months. This will work as long as the number of years between the two dates is no more than 67.

Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.

The divisor will be 1 month too long so that when you truncate the remainder it will give you one month too many. How did I figure it out? I knew it wasn't going to be perfect due to leap years and whatnot so I tested a few scenarios in Excel and 67 years was the tipping point.

Wim Gielis wrote: ↑Wed Apr 17, 2019 6:24 pm An attribute could be a solution and not too bad, but the Dimix function gives you the index number of an element in a dimension.
So if the elements are added in the correct sequence and they remain like that, a simple difference of 2 DIMIX'es is sufficient.

Makes Sense Thanks