Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Applicable88
Impactful Individual
Impactful Individual

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 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%:

Applicable88_3-1633194448284.png

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

 

 

 

 

 

 

 

 

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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
)

vhenrykmstf_0-1633500336295.png

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
Community Support
Community Support

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
)

vhenrykmstf_0-1633500336295.png

 

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.

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
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.  

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.