Calculating Machine Hours Per Day when days span multiple days

[jamiehandelman]

Hello, 

I have a dataset with 3 dates and a duration: 

- Date

- StageTimeStart

- StageTimeStop 

- Pump Time in Hours (duration between StageTimeStart and StageTimeStop). 

I need to calculate the number of hours per day that the pump is running but sometimes the StageStopTime is the day after the StageStartTime. The hours in the next day need to show up for the next day, not on the day it started. 

I've looked at other forums but have yet to find a solution that works for me. 

Any guidance would be excellent! 

[lbendlin]

use a combination of GENERATESERIES and INTERSECT  (or TREATAS).

What should happen when the pump starts or stops inside an hour, let's say at the 23 Minute mark?  What is your expected granularity?

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jamiehandelman]

Hi @lbendlin , thank you for your response. Here is a link to a PBIX file in OneDrive that has sample data. The goal would be to show hours by day, and when the pump stops inside of an hour, we would use a fraction of that hour. I.e. if the pump started at 10:30 PM on 10/24/2023, but stopped at 2:30 AM on 10/25/2023, it would show 1.5 hours for 10/24 and 2.5 hours for 10/25. 
Thank you for your help!

[lbendlin]