Getting Duration between two datetimes distributed correctly on time of occurence

[Applicable88]

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 and only can do one order after another. So it should be clear that this machine cannot work more than 24hours per day or exceeds a workload per day of more than 100%:

I take Order No. 1234 as an example: It's an order which lasted 88 hours. But only 1 hour of that duration time occured on January 1st, because it started an 2021-01-01 11PM. The problem I have is that the two bar charts aren't reflecting the right hours per day nor the right workload per day. Because the duration always falls completely on the day where the order started, which is StartTime. So the one hour workload for 2021-01-01 should be resulting in a workload of 1/24, which is 4,2%, 100% for each days in between and 15hours/24 =62,5% for that day where it finished. (2021-01-05 15PM=15 hours workload that last day) 

On the otherside Order No. 1237 just lasted 1 hour but also finished within the same day, so no problems here. 

I hope there is a possiblity to calculate so that the hours are correctly allocated to date of occurence. 

Any help is highly appreciated. 

Thank you very much. 

Best. 

PBIX File for SampleData:

https://drive.google.com/drive/folders/1dmwyfPPyoITKvQF7VtuxEJuIwJKBhSv-?usp=sharing

[Syndicate_Admin]

There are @Applicable88 ,

According to your description, 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 comprehension is disabled, further describe your requirements and provide screenshots of the desired results. I will answer your questions as soon as possible.

We look forward to your response.

Saludos
Henrio

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

[v-henryk-mstf]

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]

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. 

[Greg_Deckler]

@Applicable88 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Applicable88]

@Greg_Deckler thanks for the two links. I looked up more into the topic of "event in progress". At first I thought it is little bit similiar, but I cannot figure out how that will solve my problem. My OrderID's are by nature always closed. It already happened. It's just about the righ allocating of seconds to the right date columns. I hope someone encountered some smiliar problem. I I hope there is a workouround to this, since like the example when a duration is lasting more than several days, to return all the time duration solely on the starting date is totally wrong.