Explanation of the DaysFactor Function

To derive the value for each month's revenue forecast, a DaysFactor function is used. DaysFactor is a function with 5 arguments:

• [Start Date Field]

• [End Date Field]

• [Sub-Start Date Field]

• [Sub-End Date Field]

• "MMM-YYYY"

DaysFactor counts the days Sub-Start Date to the specified "MMM-YYYY" (assuming month end), and divides that count by the number of days in the MMM-YYYY month.

For example, the first row in our opportunity data has a Service Start of 02/14/2019.

The first formula using DaysFactor in the Source Model, Jan-18, is this:

• DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2018")

• With the values filled in, the formula is DAYSFACTOR(01/01/2018, 12/31/2019, 02/14/2019, 02/14/2021, "Jan-2018").

• DaysFactor counts the days from 02/14/2019 to the month end of Jan-2018, inclusive of the first day, and divides that count by 31 (the number of days in Jan 2018). In this case, Jan-2018 is before 02/14/2019, so the value is 0/31 = 0. The IF function regulates what to do with the resulting value of DaysFactor.

• IF DAYSFACTOR()>0.5, then return a value of [ARR]/12 otherwise return 0. Since DaysFactor for Jan-2018 is 0, then IF returns 0.

Skipping ahead in the Source Model to the field for Feb-19, the formula is:

ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2019")>0.5,[ARR]/12,0),0)

To evaluate this function:

• DAYSFACTOR(01/01/2018, 12/31/2019, 02/14/2019, 02/14/2021, "Feb-2019") returns 15 days divided by 28 = .5357.

• IF DAYSFACTOR()>0.5, then return a value of [ARR]/12. Since DaysFactor for Feb-2019 is .53, then IF returns ARR/12, or 37,705/12 = 3142.083.

• ROUND makes the result of the IF function an integer by rounding off the decimal places. This results in a final number of 3142.

When the Source Model is loaded with data and the formulas are calculated, you can see that the ARR is spread across the different months, beginning in the month of the Service Start (so long as the Service Start was in the first half of the month).

For more information about the DaysFactor function and other functions, see External Source Model Functions.