Column that calculates months left in contract starting today

[thenovice]

Hi all,

On Power BI, i would like to create a column which shows the amount of months left until the end date, starting today. I want it to also show 0 if the End Date has already passed. For example if the end date in the column says 12/31/2018 the column would return with 2. If the end date said 9/12/2018 then the column would return a 0. I tried doing the following column below.

Column = (DATEDIFF('Table'[END_DATE],'Table'[Date],MONTH)) 

This however is returning me the time in months between today and the end date and is coming out as a negative number for months left. 

[PattemManohar]

@thenovice Please try below expression...

MonthsLeft = IF(DatesTest[EndDate]<FORMAT(TODAY(),"dd/mm/yyyy"),0,DATEDIFF(FORMAT(TODAY(),"dd/mm/yyyy"),DatesTest[EndDate],MONTH))

[thenovice]

I have tried this expression, but it is coming out as red for "mm/dd/yyyy" and an error message saying "DAX comparison operations  do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values." 

[PattemManohar]

@thenovice Could you please post the DAX expression you are using...

[thenovice]

Let me know if something is wrong with that,

Thank you so much

[thenovice]

Column = IF('TABLE'[END_DATE]<FORMAT(TODAY(),"dd/mm/yyyy"),0,DATEDIFF(FORMAT(TODAY(),"dd/mm/yyyy"),'TABLE'[END_DATE],MONTH))