cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

2 ACCEPTED SOLUTIONS
Community Support

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.

Frequent Visitor

All good, i found solution 🙂

thanks!

3 REPLIES 3
Frequent Visitor

All good, i found solution 🙂

thanks!

Frequent Visitor

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

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors