Advanced view filtering using calculated fields in Dynamics 365

Advanced view using calculated fields

Happy new year!

With the new year just started, I am gonna share a recent experience in the world of Dynamics 365. I came across a requirement from a customer where they wanted to have a view filtered on opportunities where the estimated close date was the next two months from today and not the current month. They already had a separate view for opportunities closing “this” month.

Creating the new view turned out to be a bigger challenge than I thought.

With no specific filtering option in standard filtering in Dynamics, or in Fetch XML Builder (at least not what I could find), I hade to come up with a plan.

My plan

I wanted to get a proper way to filter out the specific opportunity and my plan was to use calculated field on days instead of month. Doing this I hoped to get out the exact opportunities that I wanted. The newly created calcluated field would then be field that my view was filtered on.

What I Did

  1. Created a simple date and time field with User Local behaviour setting. I named it “Hidden Close Date”.
  2. Added the field to the opportunity form (for testing purpose, I would hide it later)
  3. Created a business rule with the scope of entity where the rule was

Est. Close Date contains data
Set Hidden Close Date to Est. Close Date

(the reason why I needed to create this new date and time field is because you can’t create calculated field from the default Estimated Close Date field

  1. Created a new calculated whole number field named Days Left. I then edited the calculation, using the DiffInDays operator like this:

Days Left

  1. I added the new Days Left field to the opportunity form (for testing purpose, I would hide it later).
  2. I then created a view on the Opportunity entity set the filtering on the view to:

Days Left is greater than or Equal To 31
Days Left is less than or Equal To 90
Status Equals Open

  1. Did some testing also by creating opportunities and changing the default Estimated Close Date back and forward. I wanted to make sure that the business rule, the calculated field and the filtering on my view worked as it was supposed to.
  2. Last of all, I set my two newly created field to be hidden in the form.


This got my where I wanted to go and I could see the view with the proper filtering. The customer will never see the fields I created and everybody as happy.

If you want to know more about Dynamics 365, feel free to contact us at CRM-Konsulterna.

Kontakta oss med dina frågor!

Känner du dig osäker eller är du redo att komma igång? Kontakta oss så svarar vi på alla frågor eller föreslår ett uppstartsmöte där vi går igenom era behov.