Formula Columns have been in Public Preview since September 2022 and are supposed to be released to GA in March 2023 as part of the Power Platform 2022 release wave 2 plan. I have been working with formula columns for some time now, and although in preview have found this to be not only a great substitution to Calculated columns, but also provides much added functionality.
In a recent requirement, we needed to provide a Time Difference (in hours) as part of an SLA process between the time the record was created and the current time. This should be a pretty easy requirement, but ended up having some complications. The main reason of the complication is the behavior of the DateTime fields of CreatedOn and the availability of the current Time functions that are part of Power Fx.
Power Fx comes with a couple of functions that can provide us with the current date and time. These are the Now function and the UTC Now function. If we want to calculate the difference between the created on and the current time, we would need to use the DateDiff to get the difference between CreatedOn and Now, as shown in the function below. You will notice that I used the RoundDown as well because we needed to make sure that it does not show me a full hour until the hour has actually passed.
RoundDown(DateDiff('Created On', Now(), TimeUnit.Hours), 0)The problem with the above formula, which would have resolved the dilemma, is that the Now function is currently not supported in Formula fields, and the recommendation is to use the UTCNow function. If we were to attempt to use the same using UTCNow as shown below, we would get a different errors specifying that the operation cannot be performed on values of different behavior because CreatedOn is UserLocal and UTCNow is Time Zone Independent.
RoundDown(DateDiff('Created On', UTCNow(), TimeUnit.Hours), 0)As Formula columns are still in Preview and we have a few more months until GA, I am highly confident that the Product Group will address this issue until the product is available, but for now, I was looking for a quick resolution.
Overall it seems like we could use Calculated columns for this, but we needed to take advantage of the RoundDown function which was not available. The resolution to our problem is actually quite simple. It is not the ideal solution, but since this is a large implementation that is not due till after GA, we added a backlog item to fix this when Microsoft provides a fix for it.
The first step of the resolution was to create a DateTime Calculated column (which we called AccessedOn), where we set the value of the AccessedOn to Now (shown in the image below. Once we do that, we have an additional column in the dataverse table which will show us the same value as would be returned by the Now() function.
The next step is the use the Formula field that we were planning to use above, but replacing the Now() with Accessed On (as shown in the code snippet below)
RoundDown(DateDiff('Created On', 'Accessed On', TimeUnit.Hours), 0)The screenshot below shows you the results of the data. For the demonstration purposes I used Minutes instead of hours.
Remember that this is still in preview and not the solution you will probably expect, but we can definitely wait for GA and address this correctly.