Calculate difference between dates in Power BI

[xorpower]

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?

[v-yulgu-msft]

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

[Sean]

@xorpower  Look at my response here

http://community.powerbi.com/t5/Desktop/Measure-to-Calculate-the-number-of-days-between-two-date-col...

Just change DAY to HOUR in the formula 

Good Luck!