Calculating hours

[karkar]

Hello,

I am trying to do the difference of two dates and come up with hours in the hospital.

In the process, for some people who have been in the hospital before a particular date (say 1st January) , i would want to ignore those hours, Is it possible to acheive this in Power Bi?

For simplicity , in the examples below i am assuming that a patient came in at 12:00am (admission)  on the day specified and left at 12:00am on the dicharge date

pat    Admission         Discharge         hours

001    01JAN2017       02JAN2017       24

002    28DEC2016       05JAN2017       96   (considered only the hours from 01JAN2017)

003    31JAN2016       02JAN2017       24       

Thank you

[v-ljerr-msft · Accepted solution]

Hi @karkar,

According to your description, the formula using DATEDIFF Function (DAX) provided by PavelR above should work as expected with a little modification like below.

Assume your table is called "Table1", then you should be able to use the formula below to create a calculate column to calculate the difference between the two dates in hours.

Hours = 
IF (
    Table1[Admission] < DATE ( 2017, 1, 1 ),
    DATEDIFF ( DATE ( 2017, 1, 1 ), Table1[Discharge], HOUR ),
    DATEDIFF ( Table1[Admission], Table1[Discharge], HOUR )
)

Regards

[PavelR]

Hi @karkar

this works for me

Stay in hospital (hours) = IF(Data[Admission]<DATE(2017;1;1);DATEDIFF(DATE(2017;1;1);Data[Discharge];HOUR)-12;DATEDIFF(Data[Admission];Data[Discharge];HOUR))

Regards.

Pavel