Forum Discussion
datediff months returning incorrect values
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)
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 _bIf 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.
2 Replies
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 _bIf 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.thanks Anonymous
