Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
I am working on a report where I have to calculate the difference between two dates (a specific date, and today) then show the resulat in the following format 00years - 00months - 00days
Example:
Date1 - Today()
Date2 - 08/19/2020
Result to show => 1 year, 9 months and 19 days
Currently I am using the DATEDIF function inside Excel then importing the result in my Power BI report, but everyday I have to open the exel file in order to get the new update then refresh my report in Power BI.
Please is there any DAX fomula that can show the date difference in the above format.
Thanks.
Best regards.
Solved! Go to Solution.
@DataVitalizer Oh !! Ok, please try this (Just added two more conditions in the SWITCH statement)
Hopefully, this should fix all your test cases 🙂 Fingers Crossed !!
Final = VAR _Years = FLOOR(YEARFRAC([Date1],[Date2],4),1) VAR _Months = FLOOR(MOD(YEARFRAC([Date1],[Date2],4),1) * 12.0,1) VAR _daysinMonth = DAY(EOMONTH([Date1],0)) VAR _days = IF(DAY([Date1])<DAY([Date2]),DAY([Date2])-DAY([Date1]),DAY([Date2])+(_daysinMonth-DAY([Date1]))) VAR _daysFinal = SWITCH(TRUE(),DAY(EOMONTH([Date1],0)) = _days || DAY(EOMONTH([Date2],0)) = _days,0,_days) RETURN SWITCH(TRUE(), _Years>0 && _Months = 0 && _daysFinal = 0,_Years & " year(s)", _Years>0 && _Months>0 && _daysFinal = 0, _Years & " year(s), " & _Months & " month(s) ", _Years>0 && _Months >0 && _daysFinal >0, _Years & " year(s), " & _Months & " month(s) " & _daysFinal & " day(s)", _Years>0 && _Months = 0 && _daysFinal >0, _Years & " year(s), " & _daysFinal & " day(s)", _Months>0 && _daysFinal = 0, _Months & " month(s) ", _Months>0 && _daysFinal >0, _Months & " month(s) " & _daysFinal & " day(s)", _daysFinal & " day(s)" )
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |