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....
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....