Dynamics 365 Opportunity Revenue Forecasting – Creating Monthly Forecasts

In my last post I explored how to create a structure and a triggering flow to create Opportunity Revenue Forecasts. In this post I’ll delve into the child flow to create a set of Monthly Revenue Forecasts.

The Solution

1. Manually trigger a flow – as I explained in the last post, when we trigger this child flow we need the following elements from the triggering flow:

  1. Loops – we use the output from the DateDiff calculated field on the opportunity entity, and we’ll use this to work out how many Revenue Forecast records we will be creating
  2. Estimated Close Date
  3. Project End Date
  4. Revenue Per Day
  5. OpportunityID – The GUID for our triggering record, we’ll use this to set the Opportunity lookup field on the Revenue Forecast records we will create
  6. Estimated Revenue

Steps 2-9 – Initialize variables – the next 8 steps in the Flow are to initialize empty variables that we’ll then be setting in each Loop of our Flow. I’ve provided an outline of the Variables below:

Step No.NameTypeNotes
2startOfMonthStringUsed for calculating the first day of the Month
3endOfMonthStringUsed for calculating the last day of the Month
4daysInMonthIntegerUsed for calculating the total number of full days in a month
5workingDaysInMonthIntegerUsed to hold a count of the working days in the month
6monthRevenueFloatUsed for calculating the amount of revenue that is forecast for the month
7cumulativeAmountFloatUsed for calculating the cumulative amount of revenue to date for the forecast
8previousForecastStringUsed to hold the GUID for the forecast created in the previous loop
9loopIndexIntegerUsed to hold a count of which loop we are in for the purposes of identifying the First and Final loops

10. Do Until – The real bones of the logic of this Flow occurs within the Do Until control step. We set the Do Until to run until the value in the loopIndex variable we created above is equal to the value from the Loops input value for the Flow.

Note: the standard limits for a Do Until control are a count of 60 or a timeout of 1 hour. If you expect to create more loops then you may wish to increase these limits (though note there is an upper limit of a count of 5000 loops or a timeout of 30 days – https://docs.microsoft.com/en-us/power-automate/limits-and-config#run-duration-and-retention)

10A. Increment loopIndex – the first step in the Do Until loop is to use an Increment Variable action to increment the loopIndex variable by 1.

10B. Check if First Loop – the next step in the Do Until loop is a set of nested conditions that first checks if we’re in the First Loop (i.e. loopIndex = 1), if not it then checks if we’re in the Final Loop (i.e. loopIndex = Loops value from trigger), otherwise it carries out the intermediate Loop steps.

In the loops we set the variables we initialised above as follows:

VariableFirst LoopIntermediate LoopFinal Loop
startOfMonthEstimated Close DateFirst Day of MonthFirst Day of Month
endOfMonthLast Day of MonthLast Day of MonthProject End Date
daysInMonthendOfMonthstartOfMonth +1endOfMonthstartOfMonth +1endOfMonthstartOfMonth +1
workingDaysInMonthworkingDays CalculationworkingDays CalculationworkingDays Calculation
monthRevenueworkingDaysInMonth x Revenue Per DayworkingDaysInMonth x Revenue Per DayworkingDaysInMonth x Revenue Per Day
cumulativeAmountmonthRevenueIncrement by monthRevenueIncrement by monthRevenue
previousForecastGUID of created Revenue ForecastGUID of created Revenue ForecastN/A

I’ll run through the steps in the loops below, and call out where there are differences in the First and Final loops as identified in the table above

10A. set startOfMonth – in the first loop we set this to Estimated Close Date from the trigger inputs. In the intermediate and final loops we use an adddays expression to add one day to the endOfMonth variable to set this to the first day of the new month. The expression we use is:

adddays(variables('endOfMonth'), 1)

10B. Compose startOfMonthTicks – in order to calculate the number of days in the Forecast period we need to convert the start date and end date to their ticks representation. For this step we use the following expression:

ticks(variables('startofMonth'))

10C. set endOfMonth – in the Final Loop we set this to the Project End Date from the trigger inputs, and in the First and Intermediate loops we set it to the last day of the Month using the following expression:

addDays(startOfMonth(addToTime(variables('startOfMonth'), 1, 'Month')), -1)

This expression works as follows:

  1. Add one Month to the date from the startOfMonth variable
  2. Use the startOfMonth expression to get the first day of this month
  3. Add -1 days (i.e. subtract one day) to the resulting date to get the end date of the previous month

an example of this in action would be:

startOfMonth = 2020-04-21

addToTime(variables(‘startOfMonth’), 1, ‘Month’) = 2020-05-21

startOfMonth(2020-05-21) = 2020-05-01

addDays(2020-05-01, -1) = 2020-04-30

10D. Compose endOfMonthTicks – as at Step 10B, we need the ticks representation of the endOfMonth date, so we use the following expression:

ticks(variables('endOfMonth'))

10E – Set daysInMonth – to calculate the number of full days in the month we subtract the ticks value of the startOfMonth from the ticks value of the endOfMonth, then divide that value by 864000000000 (the number of ticks in a day), then add 1 to the value to give us the inclusive number of full days, using the following expression:

add(div(sub(outputs('Compose_endOfMonthTicks_-_First_Loop'), outputs('Compose_startofMonthTicks_-_First_Loop')), 864000000000),1)

10F – Set workingDaysInMonth – As we calculated the total number of full days in the forecast period in the previous step, we’ll set the workingDaysInMonth variable to this value, and we’ll subtract from it in the next step

10G. WorkingDays Calculation – I covered how we calculate the number of working days from the number of full days in my previous post, so you can read about that by clicking here. I’m using the exact same logic in each loop in this Flow

10H. Set monthRevenue – to set the revenue amount for the Forecast period we multiply the number of Working Days we calculated above by the Revenue Per Day from the input trigger with the following expression:

mul(triggerBody()['number_1'], variables('workingDaysInMonth'))

10I. Set cumulativeAmount – in the First Loop we set the cumulativeAmount to the monthRevenue value from above, whilst in the Intermediate and Final loops we increment the cumulativeAmount by the monthRevenue value

10J. Compose monthTitle – in order to ensure consistency of naming for the Revenue Forecast records we use a formatDateTime expression to create the title for the record:

formatDateTime(variables('startofMonth'), 'MMMM yyyy')

10K. Create Forecast – we use a Create Record action from the Common Data Service (Current Environment) connector to create the Forecast, and input the variables as follows:

10L. Set previousForecast – the last step in the loop is to set the previousForecast variable to the GUID of the record we created in the previous step. This is used to the set the Previous Forecast field on the Forecast we create in the next loop, which maintains the hierarchical link between the records. This step is obviously not required in the Final loop.

Note: if you wanted your flow to be more efficient you could remove the compose steps at 10B, 10D and 10J and just use the expressions directly where the values from those steps are used in 10E and 10K, respectively. I left the compose steps in for debugging and ease of understanding

The overall Loop section of the Flow looks like this:

Note: if you have a situation where you only have a single loop (i.e. the Estimated Close Date and the Project End Date are in the same month), then you could change the expression in the endOfMonth calculation to if(greater(addDays(startOfMonth(addToTime(variables(‘startOfMonth’), 1, ‘Month’)), -1),triggerBody()[‘date_1’]),triggerBody()[‘date_1’],addDays(startOfMonth(addToTime(variables(‘startOfMonth’), 1, ‘Month’)), -1))

11. Respond to a PowerApp or Flow – the final step in any child flow is to respond to the calling Flow. I haven’t set any outputs in this response action, though of course you may choose to if you wish.

Conclusion

Whilst this Flow may look complicated at first, I think it is actually relatively simple in reality. There are 7 main variables in the Flow (plus the loopIndex), and the nested conditions loop through these variables to set them with the expressions to create the Forecasts.

As this relies on a Do Until loop, it is not going to be instantaneous, so this is something to take into consideration if you were designing this for deployment in a production environment. In my testing, creating up to 100 Revenue Forecast records with this Flow typically took somewhere between 5-10 minutes. I’m sure this speed could be improved if you chose to code this in a plugin, but I wanted to demonstrate what could be done within the context of a Flow.

Published by

5 thoughts on “Dynamics 365 Opportunity Revenue Forecasting – Creating Monthly Forecasts

  1. Hi,
    You made an awesome tutorial.
    How did you add this grid in the Forecasting tab in the opportunity and make it show each month?

    Cheers

    Like

  2. Thanks for the reply and help Ryan.

    I followed everything what you did in the post related to this one and I am having trouble putting the “Flow button for mobile” in the switch case. Did you have similar issues? or can you help out in what am I doing wrong? Do I have to create 2 flows and then somehow input that one in here or did you do all of this in one flow?
    I am a complete noob in this so I apologise if I am asking stupid questions 😀

    Like

  3. I forgot to let you know what error I’m getting when I try to input “Flow button for mobile” in the switch case.
    You cannot use this trigger as an action.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s