Microsoft CRM 2015 is an extremely powerful application – and Advanced Find can enable a non-technical user to accomplish a lot. However, there are certainly limitations that exist, specifically when it comes to date based queries. For example, there is no clean way to query the two common examples below:
- YTD Comparisons – For example: All Orders from the Current YTD vs Last YTD
- Month Over Month Comparisons – For example: All Invoices from the Current Month vs Same Month Last Year (ex: February 2015 vs February 2014)
However, with some creativity, a few new entities and a few workflows, I’ll demonstrate how you can accomplish this type of analysis, without any code!
As the name suggests, this custom entity will represent Months. I’ve added a few custom fields to further define the Month record:
- Is Current Month – this Boolean field is used to indicate whether this month record represents the current month
- Is YTD Month – this Boolean field is used to indicate whether this month should be included in a YTD calculation. For example, when comparing 2014 to 2015 data, in March, you only want to compare January-March 2014 data with January-March 2015 data (instead of all of 2014 vs January—March of 2015)
- Next Month – this Lookup field references the next month record. For example, the March record references the April record
- End of Month Date – this Date field indicates when the current Month is over
- Sequence – this Whole Number field enables us to properly sequence the months
Again, as the name suggests, this custom entity will represent Years. There are just a few custom fields to further define the Year record:
- Is Current Year – this Boolean field is used to indicate whether this record represents the current year
- Is Previous Year – this Boolean field is used to indicate whether this record represents the previous year
There are two different categories of workflows at play – the first are used to manipulate the date entities themselves, to ensure that the Months (and Years) and properly flagged (for example: Is Current Month); the second are used to update the records that we associate these date records with (for example: adding the proper Month and Year lookups on the Order entity).
Since the month dates only changes 12 times a year – and the year, well, only one per year, the first set of workflows are definitely NOT necessary. As an admin, you could definitely make sure that you set the proper flags on the Month records manually at the beginning of the month. So, I’m going to save these for a follow up post – because the logic is still definitely interesting to look at.
However, the second set of workflows is definitely important to review. To start, we need to identify:
- The entity that we want to associate the dates with
- The date field on that entity that should be associated with our date entities
In my example below, you’ll see that we use the Created On date for the Invoice – and for each month and year combinations, we create a condition that checks to see which that Created On date fell between.
We then update the Invoice with the proper Month and Year
Building The Views
Before I walk through the views that we can now build, I’ll show what the Month and Year record looks like to provide a visual of the data points that we’re tracking.
Now for the views:
This first view, which actually references the Invoice Products record – shows how we use the YTD Month field on the Month record, which is associated with the Invoice, to only show data from January-March for both 2014 and 2015. Note, since we’re doing a year over year comparison, we can actually use the out of the box date control to include just This Year or Last Year
Current Month Comparison
This leverages the Current Month field, which will allow us to only include Invoices that are associated with February.
The result is the following dashboard which allows us to leverage these two views, which couldn’t be accomplished with the out of the box Advanced Find:
MIBAR can help you get the most out of your Microsoft CRM (Dynamics 365) Implementation. We’ve spent the last 15 years perfecting our skills as a Gold Certified Microsoft Consulting and Implementation Partner. Schedule your Microsoft Dynamics CRM consultation today!