How to calculate time between date.time and assign to correct month

[meduza]

Hello, 

Can somebody help me to find solution:
I have column Start Date with time and End Date with time, i got result working time (END.Date-Start.DATE), but as you see working can start 12/29/2023 9:02:00 AM, and end next month 1/3/2024 1:27:00 PM, so in total it it 5.18 days... but how to assign this days to December and January, i mean to split it. also need to split by truck number, as you see?
Any solution please? I tried to make date list.. but dont help me, because it is date with time... 

In final result i want to see each truck each month how many time they worked. 

[Anonymous · Accepted solution]

Hi @meduza ,

I create a table as you mentioned.

Then I create a calculated column.

Minutes = DATEDIFF('Table'[Start date],'Table'[End date],HOUR)

Finally I create a measure and get what you want.

Measure = 
SUMX (
    DISTINCT ( 'Table'[Truck] ),
    CALCULATE ( SUM ( 'Table'[Hours] ), ALLEXCEPT ( 'Table', 'Table'[Truck] ) )
)

Best Regards

Yilong Zhou

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

[meduza · Accepted solution]

All good, i found solution 🙂

thanks!

[meduza]

Thank you for time and trying to help me.
But we don't get finally what we want, i mean like truck KML296, we know that it worked 2023-12-29   09:02am till 2024-01-03   13:27pm, in total 124 hours, but we dont know how many hours was in December/2023, and January/2024.
I want to see each truck, how many hours worked each month, so this sum of hours we need to split in months, to get correct result, because started one month and ended in different month... In final table i want to see like truck KML296, December/2023, worked x hours, and January x hours...  to sum hours and group by months
I hope you get what i mean 🙂 Please let me know if you can help me
thank you

[Anonymous · Accepted solution]

Hi @meduza ,

I create a table as you mentioned.

Then I create a calculated column.

Minutes = DATEDIFF('Table'[Start date],'Table'[End date],HOUR)

Finally I create a measure and get what you want.

Measure = 
SUMX (
    DISTINCT ( 'Table'[Truck] ),
    CALCULATE ( SUM ( 'Table'[Hours] ), ALLEXCEPT ( 'Table', 'Table'[Truck] ) )
)

Best Regards

Yilong Zhou

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