I'm using DATEDIFF to calculate the # of months between two dates. It works great except it returns a value of 0 if the difference between the two dates is less than 1 month (i.e. 0-30 days). The problem is that I use these calculations later in an average and then it spits out NULL for those values that I divided by 0...
Any way to have Tableau return a decimal value for the DATEDIFF 'month' function?
Thanks in advance!
See the attached, I put together two calculated fields:
The first one still returns 1 if the months are different, the second one has day resolution so any two dates that are < 1 month apart get a decimal date that is determined by taking the average of the length in days of the two months. So a February to March has a length of 29.5 in a non-leap year and 30 in a leap year, while July to August has a length of 31 days. If you don't want that level of detail, then you could swap out that bit of the formula with Alex's suggestion of (INT([Date2])-INT([Date1]))/30. (I used to work with billing systems, so I'm used to getting very precise dates & times).
