About 4 1/2 years ago I wrote a post on how to calculate a next anniversary (birthday) date in Dynamics 365. It’s been a top 3 post according to Google Analytics. I’m proud of that solution because it was a real no-code solution to a common requirement for lots of applications (especially CRM systems.)
A client of mine recently asked for support to show the next birthday for a contact. Often there will be an ask for a list view of contacts with a birthday in the next month. In this post I’m going to show you how to use a business rule and calculated fields to achieve this functionality without writing any code. This will be a a native calculated field so it can be used in lists, forms, queries, etc.
This is not limited to birthdays, the same method use can used to calculate any anniversary date. Sale anniversaries, on-hire anniversaries, wedding anniversaries, etc. In this example I’m going to do the birthday for a contact – but this strategy can be used with any date field.
The design uses all out-of-the-box configuration capabilities. There is no code, nothing unsupported, and nothing “wacky” – just a few fields and a business rule (and a workflow if you want to update historical records.) So lets get started.
I felt it was a good solution and it worked for my client (and several others who have used this design since I wrote this article.) But it certainly isn’t ideal, it has two internal backing fields, and required a business rule to populate a field (meaning existing records wouldn’t work until their birthday field was updated.)
Fast forward to today and we have had so much evolution on the platform it seemed appropriate to revisit for a simpler solution.
The key to building a simpler solution is the introduction of Power Fx for formula columns in Microsoft Dataverse. Using Power Fx we can create a formula column that will calculate the next anniversary/birthday based on fixed date. In this post we’ll be using the out-of-the-box Contact table and the Birthday field. Our goal is to create a field that will based on the Birthday field date show when contact’s next birthday will be.
So lets get started. First, make sure you are using the preview URL for the Power Apps Maker portal (https://make.preview.powerapps.com/), this is necessary as formula columns are still a preview feature.
Click the + (plus) sign in the upper right corner to add a new field.
We’ll title the new field Next Birthday (1) and set its data type to Formula (2). If you don’t have the Formula option see above about the preview URL. When we set the data type to formula the Power Fx entry box (3) will be shown.
We’ll now use a couple nested If statements in Power Fx along with UTCToday(), to get today’s date (1), to determine if a contact’s birthday has already occurred. Yes, I explain the formula below. If it has occurred we’ll set the field to use next year and if it has not occurred (or is occurring today) we’ll set the field to use this year.
But we aren’t done yet. We need to tweek one more thing. You’ll see that Dataverse has set the column’s type to Date and Time (2), but we aren’t returning time data. If you attempt to save now you’ll get an error.
The solution is to change the format to Date Only.
Click save to add the column and your Next Birthday column should be displaying correctly, add a few records to check it out!
If you refer to my original post on how to do this with business rules and calculated fields you’ll see we’ve eliminate the need for two backing fields and the need for a business rule to calculate the initial backing field. The only column we have added is the business needed column of the next occurrence of the event. Using Power Fx formula column we’ve reduced a lot of complexity.
A big reason we could reduce this complexity is the ability to nest comparisons in an If statement in Power Fx using And and Or. I think this has a lot of potential and we haven’t even scratched the surface.
The Formula and UTCToday() and Time Zones
So date calculations are one of my favorite things although I’ll admit I prefer just dates and not dealing with time zones (I may have issues but I’m not a masochist.)
The logic of the formula is fairly simple. If the date of birth’s (Birthday) month has occurred or if it is the current month and the day has already occurred we need to calculate the birthday for next year, otherwise the birthday will occur this year.
If(
Month(Birthday) < Month(UTCToday()) Or ((Month(Birthday)) = Month(UTCToday()) And Day(Birthday) < Day(UTCToday()))
, DateAdd(Birthday,Year(UTCToday())+1-Year(Birthday),TimeUnit.Years)
, Date(Year(UTCToday()),Month(Birthday),Day(Birthday))
)We do a check to see if the date is in the past, either the month has passed or if it’s the current month then the day has passed. Then we use the DateAdd to add the appropriate number of years to the original date of birth. If their birthday has already occurred this year it’s their current age plus one year otherwise it is the age they will turn this year. We get that number by subtracting the current year from their birth year (and adding 1 if their birthday has already occurred.)
I took that screen shot at 10:34 PM on July 22, 2022 in the US Eastern time zone. You’ll notice that Jane Janet is showing as not having a birthday until next year even though her birthday is occurring today and should show as today.
This is a limitation of the UTCToday() function to get today’s date. It ignores your local timezone. So while it’s July 22nd at my desk in the world of UTC time it is already July 23rd.
From a business process standpoint this is the right choice. You don’t want a formula column – which is essentially virtual – to be changing based on the time zone of the user viewing the record. But it is something to be aware of, especially as you get larger gaps between the local time of your users and UTC.
And, no, there is not an option in the Formula columns to user a user local date. It is UTCToday() or nothing.
What about Leap Years?
Yes, leap years can be a problem and I have simply ignored that problem in the calculation above.
Here is the reality, if you have a leap year baby (i.e., born on February 29th) their birthday will show as February 28th in non-leap years.
But that doesn’t solve the “leap year problem”. The problem is whether you should treat such a person’s non-leap year birthday as being February 28th or March 1st. There is no consensus. Go Bing it if you want – there is no clear answer.
It may actually matter for the system you are building. If you are just sending out birthday cards it probably doesn’t matter. But, if you are building a system to issue driver’s licenses or sell people age restricted items this probably matters a lot to you.
If your use case requires that a leap year birthday be shown as March 1st, instead of February 28th, I’ve got an answer for you but it’ll have to wait for the next blog post – because it’s a wee bit more complicated.
Best of luck and have fun with Power Fx Formula columns in your Model Drive Power Apps!
If you have any questions please ask them in the comments!
