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.
Hello Guys,
I need to calucate difference between 2 dates and the output should be in hours and minutes. It is possible that one of the date columns could be null/blank.
I have used DATEDIFF function but because one of the date could be blank and/or the difference between both the dates could be negative (i.e. Start Date is less than End Date) I am unable to calcuate the output using DATEDIFF function.
As you can see in below figure there are 3 columns with dates; column A, B & C. The result set that I want is also attached below. The result set is Column B - Column A.
Can you please suggest how to handle the blanks and what to do if column A is lower value than column B?
Hi @xorpower,
Following Sean's suggestion, you can get the correct difference between two date columns, considering the scenation where one of the date columns is blank or column B is lower than column A.
Moreover, if you want to display values in format "00:08 Hours", please refer to below formulas.
Difference = SWITCH ( TRUE (), 'Date Difference'[Start Date] < 'Date Difference'[End Date], DATEDIFF ( 'Date Difference'[Start Date], 'Date Difference'[End Date], MINUTE ), 'Date Difference'[Start Date] > 'Date Difference'[End Date], DATEDIFF ( 'Date Difference'[End Date], 'Date Difference'[Start Date], MINUTE ) * -1, 0 ) HourMinute = IF ( 'Date Difference'[Difference] = BLANK (), BLANK (), FORMAT ( ( 'Date Difference'[Difference] - MOD ( 'Date Difference'[Difference], 60 ) ) / 60, "0#" ) & ":" & FORMAT ( MOD ( 'Date Difference'[Difference], 60 ), "0#" ) & " Hours" )
Best regards,
Yuliana Gu
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |