Aggregate/slice hours between two date time values by month

[FlossBI]

Hi,

I've got a table where I'm calculating working hours outage between two date/time stamps from Dataverse.  The durations range from a few hours to a few weeks and can start between 6am and 6pm on any working day and can also start on the hour or 30 minutes past.  I've got a calculated column in DAX that gives me the working hours, but I've realised that where the duration is spanning different months (and potentially financial years) I can't give end users the number of working hours outage in current month and so on.  

In this example, there is a start date in October and the end date in November.  Using the month of start date in a table it's giving a total of 182 hours using the DAX calculation - but that's the whole outage duration, not just the hours in October.  

But if we could aggregate it by the months it would look more like this:

I thought about using Power Query and listing the individual days between the datetimes but that doesn't work when the duration is only a few hours?  I've tried using power query list.datetimes with an hour interval but that doesn't work for 30 minute past the hour values. I tried 30 min intervals between the two datetimes but I can't seem to get it to work as it won't list everything in the duration for example List.DateTimes([Start], Duration.Days([End] - [Start]), #duration(0,1,30,0)) for the values above returns about 21 rows

I'm also not sure how I would calculate the working hours in the table with the list like this rather than start and end columns.  Ideally we'd just list the days out rather than the 30 intervals as I'm worried about the size of the table and the calculations when it's a long outage, but as mentioned above that's tricky when it's out for a few hours.

I've uploaded the pbix file

https://1drv.ms/u/s!As7wPfxult_enVcuojW02yMTbN7t?e=FdlXtQ

 I would be very grateful for any inspiration or advice please.

[FlossBI]

Thanks @v-binbinyu-msft 

I have mutiple outage records just with a start datetime and end datetime.  I am measuring the duration between them in working hours.  The duration varies and can last a few hours through to a few weeks.

I would like to give users the outage duration working hours by month and financial year.  But with only the start and end datetime, if the duration spans more than one month for instance I can't work out how to do it.

[v-binbinyu-msft]

Hi @FlossBI ,

I'm a little confused about your needs, Could you please explain them further?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu