How to calculate hours per date from start datetime and end datetime

[bibhash_21]

Hi,

Some shifts will cross midnight.  These will have to be seperated by calendard day
For example: clock in at 9PM on 11/19/19 and clock out at 9AM on 11-20-19.  this should show 3 hours on 11-19-19 and 9 hours on 11-20-19.  this reproting will have to combine hours from all events that occur in that calendar day

My Table

table1

Event Name	Start DateTime	End DateTime
Event1	11/22/2019 21:00	11/23/2019 9:00
Event2	11/22/2019 16:00	11/23/2019 15:00
Event3	11/24/2019 17:00	11/26/2019 3:00
Event4	11/25/2019 10:00	11/25/2019 18:00
Event5	11/25/2019 19:00	11/25/2019 23:00

and want like this

Event Name	Date	time(hours)
Event1	11/22/19	3
Event1	11/23/19	9
Event2	11/22/19	8
Event2	11/23/19	15
Event3	11/24/19	7
Event3	11/25/19	24
Event3	11/26/19	3
Event4	11/25/19	8
Event5	11/25/19	4

pls give me dax solution

[v-lionel-msft · Accepted solution]

Hi @bibhash_21 ,

My original data table (Append3):

In 'Edit Query':

First, get this table:

My method:

copy the original table,

then ,swap the names and positions of the two columns (Start date & End date),

then ,use 'Append' feature,

then, change 'Data type' of 'Date1' (I also change the name of the column )

then, choose 'Event Name' column and 'Data1' column, click ' Remove duplicate rows',

then, you can get ‘Append3’.

Second, get this table:

My method: https://www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/ 

If you want to complete the step, you'd better get another data table which don't have time, like this table:

In 'Data View':

First, create a column in 'Append3':

Time = 
24- HOUR(Append3[Date2])

Then, create a column in 'Sheet5':

Column = 
LOOKUPVALUE(
    Append3[Time],
    Append3[Date1], Sheet5[Custom],
    Append3[Event Name], Sheet5[Event Name]
)

Time = 
IF(
    Sheet5[Column] <> BLANK(),
    Sheet5[Column],
    24
)

Best regards,
Lionel Chen

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

[bibhash_21]

Thank you so much @v-lionel-msft  for reply

but the time calculation is wrong.

Time should be

Event Name	Date	time(hours)
Event1	11/22/19	3
Event1	11/23/19	9
Event2	11/22/19	8
Event2	11/23/19	15
Event3	11/24/19	7
Event3	11/25/19	24
Event3	11/26/19	3
Event4	11/25/19	8
Event5	11/25/19	4

Pls reply