datediff months returning incorrect values

[akhaliq7]

I am trying to get the duration between two dates in months and I am getting some inconsistent results

e.g.

start date	end date	duration in months
14 June 2022	05 July 2022	1
21 October 2023	12 January 2024	3

The above two durations are incorrect as whole months have not been taken into account.

Dax
Duration = DATEDIFF(start_date, end_date, MONTH)

[Anonymous · Accepted solution]

Hi @akhaliq7 ,

Regarding your question, 'DATEDIFF' calculates the difference between two date months. Even if 'EndDate' is the first day of the new month, it counts as an additional month. You can modify your expression.

Column = 
VAR _a = DATEDIFF([start date],[end date],MONTH) 
VAR _b = IF(DAY([end date]) >= DAY([start date]) , _a ,_a - 1)
RETURN _b

If my understanding is wrong, please state your expected outcome.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akhaliq7]

thanks @Anonymous