Diffinmonths doesn't return correct number in some cases

[DebbiLawson1]

I have an whole number "number of months" calculated field using the DiffInMonths function using the Period of Performance (PoP) start date and PoP end date. For dates that fall in the middle of the month, like  Jul 20 2022 - Jul 19 2023., the function properly returns 12. BUT for start dates like 1/1/23 and end date 12/31/24, it returns 11 months. Suggestions on how to solve this?

[AhmedSalih]

Hello, @DebbiLawson1, I tried to create your use case and I got 23 months using your example:

Start date 1/1/2023 and end date 12/31/24:

Can you provide a screenshot of your formula?

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[DebbiLawson1]

Thank you @Ahmed for confirming my issue. You are right - those dates are 24 months apart, but only 23 is returned. Here is the screenshot of the formula.

[AhmedSalih]

@DebbiLawson1 , yes because DIFFINMONTHS Returns the difference in months between two Date and Time columns and If both dates and times fall on the same month, the difference is zero. This last hyperlinked part means it wont consider the days, only the month number. So from January 2023 to December 2024 is 23 months.

I hope it is clear for you now.

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[DebbiLawson1]

So, @AhmedSalih (or anyone else who cares to answer), my real question is: how to calculate what a human would understand to be the difference in months? I can't just add 1 to the current calculation because that won't be accurate in every case (like the first example I gave in my original post), where DiffinMonths works as expected). 

[AhmedSalih]

@DebbiLawson1, in Dataverse there is no out-of-the-box feature can do this for you. A way to do this is to create a Column (call it for example End-of-Month) that will will determine if the End-Date days number is the last day of the month number in the date and put a value like Yes. Then you can use the calculated Column with a condition to check if  End-of-Month ="Yes", your DIFFINMONTHS ()+1. But to create such column (End-of-Month), you will have to use Power Automate Flow trigger for live calculation, or if you are importing your data from an external data source, you can use PowerQuery. 

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[DebbiLawson1 · Accepted solution]

@AhmedSalih., thank you for your replies to my message. I want to let you (and others) know that the solution I have found is to use DiffinDays calculation, then divide by 30. This seems to work in all the situations I've tried, probably because the datatype is whole number, so I presume the system rounds it up to the correct number of months. Thank you again for your help!