calculate how many hours between dates for each day separated

[MNASRMOH]

Hello,

I need help with creating the Dax formula to calculate how many hours between two dates for each day separately.

for example 

the below screenshot showing the employee has shift from 5 PM on the 10th of July to 2 AM the next day [9 hours], and take into consideration there is a relationship between this tale and calendar table and date is key column.

and the below screenshot showing the result when I use the matrix to visualize the data, showing 9 hours scheduled on the 10th of July which is wrong, it's supposed to be 7 hours only and 2 hours remaining must be moved to the day.

Thanks,

Nasr.

[v-angzheng-msft · Accepted solution]

Hi, @MNASRMOH 

Try to create a calculated column like this:

Scheduled = 
DATEDIFF('Data'[Starts],'Data'[Date]+1+TIME(0,0,0),HOUR)

data:

result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MNASRMOH , are you looking to calculate business hours ?

refer if this can help

https://exceleratorbi.com.au/calculating-business-hours-using-dax/