Days in Month – How to Easily Calculate in a TM1 Rule
Calculating the number of days in a month is used for a number of financial calculations, such as inventory days and debtor days. Have you ever wondered though how to calculate how many days in every month for a model?
Calculate the Number of Days in a Month
The solution to this problem involves using the DayNo function in combination with attributes on both the Year and Month dimensions.
So firstly, I have a Month dimension with an attribute called Next Month and a Year dimension with an attribute Next Year. So they appear like this:
We then use those in the TM1 Rule to calculate the number of days in the month.
['Days in Month'] = N: IF ( !Month @<>'12'
,DayNo( !Year |'-'| ATTRS('Month',!Month,'Next Month') |'-01') - DayNo( !Year |'-'| !Month |'-01')
,DayNo( ATTRS('Year',!Year,'Next Year')|'-'| ATTRS('Month',!Month,'Next Month') |'-01') - DayNo( !Year |'-'| !Month |'-01')
The core of this is using the DayNo function. The syntax for that is DayNo (‘YYYY-MM-DD’), so we combine the year being evaluated (say ‘2018’), get the Next Month number from the attribute (so ’09’, if we are in August) and append ’01’ for the day, representing the first day of the month. Then we subtract the same calculation for the DayNo for the current month and bob’s your uncle!
Obviously we need to test if we are in December or not and if so, then use a slightly different calculation. Thus the opening IF statement.
Days between Dates
Obviously a similar method could be used to calculate the number of days between two dates.