Forum Discussion
DATEDIFF month interval not working as expected.
To get more accurate "MONTHDIFF", you can create a column like below:
Cancellation Month = ( 1 * ( [CANCELDATE] - [POLICYSTARTDATE] ) ) / DAY ( EOMONTH ( [CANCELDATE], 0 ) )Regards,
Community ChampionYour understanding is incorrect. DATEDIFF counts the "interval boundaries" crossed between the two dates. Therefore, going from Jan 31st to Feb 1st crosses the month interval boundary (new month).
How would you get it so that the function returns 0 if the difference between the two dates is less than one exact month? For example, if the start date is Jan 31st and the cancelled date is 1st Feb, we get 0 rather than 1.
Thanks!
- Greg_Deckler
Well, that's sort of going to depend on your definition of 1 exact month I suppose. You could probably get close with something like this:
Column = MROUND([DateCancelled]-[Date]*1.,30)/30
Thank you.
IBM gives the description of the DATEDIFF function on their website...
"The result will be the integer portion of the number of months between the two dates, with any fractional component truncated."I will have to count number the number of days instead as a workaround.
v-sihou-msftMicrosoft Employee
To get more accurate "MONTHDIFF", you can create a column like below:
Cancellation Month = ( 1 * ( [CANCELDATE] - [POLICYSTARTDATE] ) ) / DAY ( EOMONTH ( [CANCELDATE], 0 ) )Regards,
- Greg_Deckler
IBM is not the authority on DAX:
This is the authority:
https://msdn.microsoft.com/en-us/library/dn802538.aspx
