Forum Discussion

PowerPaddy's avatar
PowerPaddy
Frequent Visitor
9 years ago
Solved

DATEDIFF month interval not working as expected.

Hello again,   I'm using the DateDiff function to work out after how many months a policy was cancelled but it's not giving me the expected results. Cancellation Month = DATEDIFF([POLICYSTARTDAT...
General Comment
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    PowerPaddy

     

    To get more accurate "MONTHDIFF", you can create a column like below:

     

    Cancellation Month =
 ( 1
    * ( [CANCELDATE] - [POLICYSTARTDATE] ) )
    / DAY ( EOMONTH ( [CANCELDATE], 0 ) )

    Regards,

Harry_Long2's avatar
Harry_Long2
Regular Visitor
9 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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