Checking blocks of times/dates - is there an easier way?

[Walt1010]

I am working with shift information, and have to check whether a shift (given start and end datetimes) falls partly or fully within a time and date period.

For example, a particular period I need to check for could be Xmas Eve from 17:00. If I have a shift from 24/11/2024 8:00 to 24/11/2024 20:00, then I need to determine that is on a matching date (24/12/2024), and that the shift falls partly within the designated period, by 3 hours.

I achieve this currently by way of much crude condition-checking on the start and end times of the the shift and the period I'm matching it to, in M language.

Is this a type of problem for which there is some functionality that could simplify the effort, or a clever technique?

[Anonymous]

Hi, @Walt1010 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

It's a typical COUNTROWS(INTERSECT()) pattern.  What's the granularity of your data? Does it go to minute level, or further down?  You may want to memorize numbers like 1440 and 86400.

[Walt1010]

The data consists of about a thousand rows, each row being a shift with start and end dates and tiimes. I need to test if the shifts overlap with 4 date/time periods, andif so, to what extent (with in hour units, with 30 minutes being the smallest unit), as follows:

Xmas eve from 17:00  New Years eve from 17:00 Xas Day 25/12/2024 all day New Years Day 1/1/2025 all day.

At the moment I'm doing it in M, and its a labourious process.

[lbendlin]

Should not be an issue at all with this high granularity level.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.


Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Tutu_in_YYC]

I'd do it using DAX either in calculated column or measure depending on how you want to visualize it. But it will be the same approach, compare dates, and it is overlaps, find the difference in hours.