Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@thenovice Please try below expression...
MonthsLeft = IF(DatesTest[EndDate]<FORMAT(TODAY(),"dd/mm/yyyy"),0,DATEDIFF(FORMAT(TODAY(),"dd/mm/yyyy"),DatesTest[EndDate],MONTH))
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...
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))
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |