Down Time Calculation with Overlap timings ignoring

[Anonymous]

[Anonymous]

Here is problem with Overlap time. For example take the Machine 8 was open on  12/4/2020 8:36:00 PM and closed on 12/6/2020 12:30:00 AM. In this case12/5/2020 is completely fall under down time. And Machine 9  was open on 12/5/2020 1:35:00 PM  and closed on 12/6/2020 11:59:00 AM but this complete hours is already covered in the Machine 8 so no need to count this hours.

[V-lianl-msft]

Hi @Anonymous ,

Please refer to the blog:

https://datachant.com/2019/07/22/find-overlapping-time-periods-using-powerquery/ 

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please remove any sensitive data before uploading.

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

[Anonymous]

Hi @V-lianl-msft  Thanks for spending your time on this task. Here is the file.  https://drive.google.com/file/d/1WwEdw7pFDKn_bK70W0FEnIcn2iTThKu_/view?usp=sharing 

[V-lianl-msft]

Hi @Anonymous ,

Could you please share the file again? I don't have access to this file

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

[Anonymous]

Hi @V-lianl-msft  Please find the attachment here: https://drive.google.com/file/d/1WwEdw7pFDKn_bK70W0FEnIcn2iTThKu_/view?usp=sharing 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

In the query editor, I first expand the date into multiple rows, and then create two columns through DAX to get the last start and end time respectively.

The calculated logic is in the result column.

Please refer to the sample pbix.

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

[Anonymous]

@V-lianl-msft  Thank you very much.

And one more thing I would like to mention  here. How can I achive this with the seconds instead of Hours.? And if this the only way I wanted to implement it on my fact table which is huge volume of data. Is there any other way creating measure.?