Forum Discussion

xorpower's avatar
xorpower
New Member
9 years ago

Calculate difference between dates in Power BI

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?

 

 

 

 

 

 

 

 

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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