Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thenovice
Frequent Visitor

Column that calculates months left in contract starting today

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. 

 

 

Capture.PNG

5 REPLIES 5
PattemManohar
Community Champion
Community Champion

@thenovice Please try below expression...

 

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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Let me know if something is wrong with that,

 

Thank you so much

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.