Microsoft Dynamics 365 | Integration, Dataverse...

SUBTRACT NUMBER OF MONTHS FROM TODAY

(4) Share

Report

Posted on by Jenn_STEP

126

Hi All,

I want a field that will give me a whole number of the months that have passed this financial year. So for example the financial year starts in April and it's currently May. Therefore 1 month has passed, so my field should equal 1.

Therefore I want a calculated field which takes today's date Now() and - 4 off the month number and returns the value.

I have tried SUBTRACTMONTHS(NOW(), 4) but I receive this error:

You can't use Now(), which is of type dateTime, with the current function.

I have tried SUBTRACTMONTHS(4, NOW()) but I recvie this error:

You can't set the value SubtractMonths(4, Now()), which is of type dateTime, to type integer.

Does anyone know how this could be achieved?

Categories:

Dynamics 365 general

I have the same question (0)

All responses (6)

Answers (0)

André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

Like (2)

Report

Hi,

You posted your question in a general forum, so we don't know which exact Dynamics 365 solution you are using. Can you please tell us what Dynamics 365 solution you are using?

Was this reply helpful? Yes No
Suggested answer

Daivat Vartak (v-9d... 7,835 Super User 2025 Season 2 on at

Like (2)

Report
Hello Jenn,

You're very close! The issue is indeed with the NOW() function returning a DateTime, and SUBTRACTMONTHS (and similar date/time functions) not directly producing the integer you need. Here's the correct approach using a calculated field in Dataverse:

IF(MONTH(NOW()) >= 4, MONTH(NOW()) - 3, MONTH(NOW()) + 9)

Explanation:

IF(MONTH(NOW()) >= 4, ... , ...): This checks if the current month is April or later (month number 4 or greater).

MONTH(NOW()) - 3: If it's April or later, we subtract 3 from the current month number. This is because April is the 4th month, so April - 3 = 1 (the first month of your financial year), May - 3 = 2, June - 3 = 3, and so on.

MONTH(NOW()) + 9: If it's before April (January, February, March), we add 9 to the current month number. This is because your financial year starts in April, so January is the 10th month of the previous financial year, February is the 11th, and March is the 12th.

How to Implement in Dataverse:

Create a Calculated Field: In your Dataverse table (e.g., the table where you want this field), create a new field. Set the field type to "Whole Number."

Set the Field Calculation: In the field's properties, set the calculation type to "Calculated."

Enter the Formula: Paste the formula above into the formula editor:

IF(MONTH(NOW()) >= 4, MONTH(NOW()) - 3, MONTH(NOW()) + 9)

Save and Publish: Save and publish the changes to your table.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Best Regards,

Daivat Vartak

Was this reply helpful? Yes No
DenisMT 33 on at

Like (0)

Report

Agreeing to Daivats solution.

Please keep in mind: Calculated fields may be deprecated in future. It is best to switch to Power Fx dataverse columns whenever possible.

Unfortunately I cannot provide the right formula right now, but these docs should help:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns?tabs=type-or-paste

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-now-today-istoday

Was this reply helpful? Yes No
Jenn_STEP 126 on at

Like (0)

Report

Hi Both,

When i enter this formula I get an error 'The formula contains invalid characters.'

Was this reply helpful? Yes No
Suggested answer

Aymen CHELBI 574 on at

Like (0)

Report
Hello,

To calculate the number of months that have passed in the financial year (which starts in April), you can try using a different approach to handle the Now() function and the SUBTRACTMONTHS function correctly.

Here's a formula that can help:

First, you need to extract the month from the current date.

Then, subtract 3 from the current month to adjust it for your financial year (since your financial year starts in April, which is month 4).

Finally, the result will give you the number of months that have passed.

example formula :

MonthsPassed = MONTH(TODAY()) - 3

MonthsPassed = IF(MONTH(TODAY()) - 3 < 1, 0, MONTH(TODAY()) - 3)

Best regards,

Was this reply helpful? Yes No
Jenn_STEP 126 on at

Like (0)

Report

@Aymen CHELBI

This does not work i receive the error:

The formula contains invalid characters.

Was this reply helpful? Yes No