DAX Calculate Number of Months

Hi Friends,

I was working on a dataset where I need to calculate number of months between 2 dates. I was using SQL Server 2012/14 so was not able to use newly introduced DATEDIFF function for that.

So, how to calculate Months based on 2 dates using DAX? Well below is the formula for that

DataSet
DATA_DATE                                     FUTURE_DATE
09/30/2016                                         12/1/2019
09/30/2016                                         1/1/2020
09/30/2016                                         2/1/2020
09/30/2016                                         5/1/2020

Expected
DATA_DATE                                     FUTURE_DATE        MONTHS
09/30/2016                                         12/1/2019                    39
09/30/2016                                         1/1/2020                      40
09/30/2016                                         2/1/2020                      41
09/30/2016                                         5/1/2020                      44

Solution
Below is the formula to calculate Months

=(YEAR([FUTURE_DATE])-YEAR([DATA_DATE]))*12+MONTH([FUTURE_DATE])-MONTH([DATA_DATE])

Incase we have some blanks in "FUTURE_DATE" (could be a case) then use BLANK() function.

=IF([Calendar]<>BLANK(),(YEAR([FUTURE_DATE])-YEAR([DATA_DATE]))*12+MONTH([FUTURE_DATE])-MONTH([DATA_DATE]),BLANK())

Happy Learning....