Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
4 years ago

Getting Duration between two datetimes distributed correctly on time of occurence

Hello, I want to ask how I can calculate the duration correctly on the time of occurence. For example I have sample data in the screentshot. Its about a machine which is constantly fetching boxes an...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
4 years ago

Hi Applicable88 ,

 

According to your description, please try the following MEASURE formula:

work = 
IF (
    DAY ( MAX ( 'Order'[FinishTime] ) ) = DAY ( MAX ( 'Order'[StartTime] ) ),
    ( HOUR ( MAX ( 'Order'[FinishTime] ) ) - HOUR ( MAX ( 'Order'[StartTime] ) ) ) / 24,
    ( 24 - HOUR ( MAX ( 'Order'[StartTime] ) ) ) / 24
)

 

If the understanding is off, please further describe your requirements and provide screenshots of the desired results. I will answer your questions as soon as possible.

 

Looking forward to your reply.


Best Regards,
Henry


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

  • Applicable88's avatar
    Applicable88
    Impactful Individual
    4 years ago

    Hello v-henryk-mstf, thank you very much for the effort. The values are still not correct. As you can see the first order spans over 3,5 days, but between the 2021-01-01 and 2021-01-05 are not bars. The time we got returned here, are also not how it should be displayed.

     

    I try to describe in detail:

    OrderID 1234 last 88 hours in total. It started on 2021-01-01 at 11PM. The first chart is correct, since 1 hour is allocated to the first starting day:  1hour/24hours are 4,17%

    Afterwards there should be 3 other bars for 2021-01-02 until 2021-01-04 each day 100% workload. On 2021-01-05 which is the last day of that order, the machine runs 15 hours, so in workload percentage result in 15/24 =62,5%.

     The most important thing is, that the machine runs orders one after another, but never at the same time, which actually returns clear data. 

     

    I hope there is a way to compute it that way and also for the other orders. Of course a order can also start and finish same day.

    Best regards.