Is Goal Recalculation Time-zone Independent? Well, maybe not.

One of the nice features in Dynamics CRM when it comes to dates, is that it stores everything in Universal Time. For the most part, this works pretty well until you run in the following scenario related to goal recalculation.

Let’s suppose you have teams of salespeople grouped by regions, and that you have members in those teams that have goals not tied to any direct sales but goals assigned to the entire team, for example administrative staff. In this scenario, you would create goals with the following characteristics:

• Goal Metric: Amount of Sales
• Goal Owner: Admin staff
• Target: Fixed Dollar Amount
• From: Fixed Date
• To: Fixed Date
• Rollup Only from Child Goals: No
• Record Set for Rollup: All (sales records owned by all team members)
• Rollup Query: A query to filter by region

In this case, the expectation would be that all administrative staff shows the same progress at any moment. Well, at least that’s what I thought until I looked at the following chart:

Figure 1 – Goal Progress Chart

It’s obvious that the progress for the first member is different from all others. After verifying that all the goals were exactly the same and have been recalculated around the same time, I was running out of ideas.

Fortunately, this is a CRM on premise implementation, so I had more chances to look at the server at a deeper level. The CRM async service was running OK, and no errors were found in the event log or CRM trace.

So I needed to dig even deeper. I ran a SQL Profiler session, and decided to recalculate the goals for the first and second person in the team. After a few hours of research, I found the smoking gun:

Figure 2 – Excerpt query person 1

Figure 3 – Excerpt query person 2

It turns out that person 1 had his time-zone set for Central Standard Time, whereas person 2 had his time-zone set for Eastern Standard Time; therefore, the query for person 1 was missing all transactions for the first day in the time range, given that all sales data only records the date of the transaction and the data is imported using Eastern time.

Now that I found this, at least I know what caused the discrepancy, and the fix is as simple as making every team member use the same time-zone. However, that may not be a realistic scenario. You could perfectly have teams that have members across different time-zones, in which case the goal recalculation should really be time-zone agnostic. That’s up for debate or perhaps to have the Dynamics product team take a look at.

Advertisement