Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How to reference last ...
Microsoft Dynamics CRM (Archived)

How to reference last date of year in Calculated field

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Greetings. We sell long term projects and services, which last month to years. In order to track progress toward yearly sales goals I need to create a field which provides Estimated Revenue for an Opportunity for the remainder of THIS year.

I have calculated Estimated Monthly Revenue by dividing the total estimated revenue by the length of the project in months.  Now I need to calculate the number of months between the Job Start Date and the end of the year, so that I can multiply months by monthly revenue.  However, I can't just enter the date of 12/31/17 in the calculation.   (I'd like to use DIFFMONTHS (JobStartDate, 12/31/2017)  I also can't use a trim to get the month of the job start date.

Any suggestions on how I can do this math?  I'm not against creating a calculated field which is just the last day if the year. But I haven't been able to figure out how to do that either.

*This post is locked for comments

  • Suggested answer
    ashlega Profile Picture
    ashlega 34,475 on at
    RE: How to reference last date of year in Calculated field

    Hi,

      you might also create a business rule.. or a workflow..

      It would be a set of conditions:

      IF ( Job Start Date < 12/31/2017) SET YEAR_END = 12/31/2017

      ELSE   IF ( Job Start Date < 12/31/2018) SET YEAR_END = 12/31/2018

      ELSE   IF ( Job Start Date < 12/31/2019) SET YEAR_END = 12/31/2019

      ELSE   IF ( Job Start Date < 12/31/2020) SET YEAR_END = 12/31/2020

    etc.. you just need 4-5 lines like that, and you should be good for the next few years at least. You can use a workflow or a business rule for this:

    20323.1.PNG

  • Suggested answer
    Ivan Ficko Profile Picture
    Ivan Ficko 1,380 on at
    RE: How to reference last date of year in Calculated field

    Most of the custom workflow steps regarding date time fields can be found in this collection

    github.com/.../CRM-DateTime-Workflow-Utilities

    I'm sure that you can pick few steps to do the math you need here.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans