Microsoft Dynamics 365 | Integration, Dataverse...

SUBTRACT NUMBER OF MONTHS FROM TODAY

(4) Share

Report

Posted on by Jenn_STEP

118

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

All responses (6)

Answers (0)

Jenn_STEP 118 on at

Like (0)

Report

SUBTRACT NUMBER OF MONTHS FROM TODAY

@Aymen CHELBI

This does not work i receive the error:

The formula contains invalid characters.
Suggested answer

Aymen CHELBI 344 on at

Like (0)

Report
SUBTRACT NUMBER OF MONTHS FROM TODAY

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,
Jenn_STEP 118 on at

Like (0)

Report

SUBTRACT NUMBER OF MONTHS FROM TODAY

Hi Both,

When i enter this formula I get an error 'The formula contains invalid characters.'
DenisMT 33 on at

Like (0)

Report

SUBTRACT NUMBER OF MONTHS FROM TODAY

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
Suggested answer

Daivat Vartak (v-9d... 3,110 Super User 2025 Season 1 on at

Like (2)

Report
SUBTRACT NUMBER OF MONTHS FROM TODAY

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
André Arnaud de Cal... 293,245 Super User 2025 Season 1 on at

Like (2)

Report

SUBTRACT NUMBER OF MONTHS FROM TODAY

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?