Do you have a need to calculate an Average Daily Balance? Perhaps you need to calculate Return on Average Assets YTD? For these types of formulas, you often need metrics such as “Number of Days in Month”, “Number of Days YTD” or “Number of Days in Year” to facilitate the proper calculations.
Conceptually, these are fairly simple formulas, however, you don’t want to have to write out exhaustive logic to calculate each months’ number of days. And what do you do for “Days in Year” when it’s a leap year? These are the types of formulas that can trip you up in an excel spreadsheet. Thankfully in Workday Adaptive Planning, you can utilize some dot notation functions, which employ your model’s Time Stratum to calculate these calendar days for you. No more hard-coding years to determine if there are 365 or 366 days in the year!
Time Assumptions
Here is an example of some of the Time assumptions we needed on a recent engagement. We use these values to serve as denominators in some of our ratios and KPI’s.
We utilized the Time Stratum and dot notation to generate all these values, therefore not needing to hardcode any days, months, or years into any formula that needed these values. Notice that the Year 2020 is a leap year, and its “Days in Year” automatically reflects 366 days in the year.
Here are the formulas used for each of these calculated elements:
Month of Year: this.year.positionof(this.month)
Days in Current Month: this.month.numberofdays
YTD Number of Days: if(ACCT.Month_of_Year = 1, ACCT.Days_In_Month, ACCT.Days_In_Month + ACCT.YTD_num_days[time=this-1])
Days in Year: this.year.numberofdays
For a complete review of all dot notation functions available, view the Adaptive Planning documentation for Dot Notation functions.
We have included some of the most commonly used functions in the screenshot below.
