Skip to main content
cancel
Showing results for 
Search instead 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

Reply
meduza
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. 

 

meduza_0-1716456509528.png

 

2 ACCEPTED SOLUTIONS
v-yilong-msft
Community Support
Community Support

Hi @meduza ,

I create a table as you mentioned.

vyilongmsft_0-1716519046332.png

Then I create a calculated column.

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

vyilongmsft_1-1716519153359.png

Finally I create a measure and get what you want.

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

vyilongmsft_2-1716519248068.png

 

 

 

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.

View solution in original post

meduza
Frequent Visitor

All good, i found solution 🙂

thanks!

View solution in original post

3 REPLIES 3
meduza
Frequent Visitor

All good, i found solution 🙂

thanks!

meduza
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

v-yilong-msft
Community Support
Community Support

Hi @meduza ,

I create a table as you mentioned.

vyilongmsft_0-1716519046332.png

Then I create a calculated column.

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

vyilongmsft_1-1716519153359.png

Finally I create a measure and get what you want.

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

vyilongmsft_2-1716519248068.png

 

 

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors