Split duration by hour of day

[MichalSimota]

Hi all

I need to solve, how to split duration of downtime by hour of day. So duration of downtime will be assigned to hour, when it happened and not just to start or end hour.

This is table with original data:

Thanks to help in this thread, I'm able to split it, but have issue when downtime starts at one day and ends next day. Then it is hidden and excluded from data.

Previous threat with duration split

Here is table what I want to achieve with examples of data I have problem with:

Many thanks

Michal

[Greg_Deckler]

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

[MichalSimota]

I have biggest problem with formula you also have in those codes (in Filter). I have in code this formula:

Start <= hour of day&&end >= hour of day

But when start is at 23:00 one day and end is 01:00 another day I'm out of this range. I need to add something like:

when start<>end then hour of day>= start OR hour of day<=end

As I'm new in DAX i don't know how to build such condition.

Many thanks

Michal

[v-yuta-msft]

Hi MichalSimota,

"But when start is at 23:00 one day and end is 01:00 another day I'm out of this range. I need to add something like:

when start<>end then hour of day>= start OR hour of day<=end"

<--- How can hour of day be compared with start or end? Hour of day in your table is number type while start and end is date type. In addtion, could you post the expected result in your sample table?

Regards,

Jimmy Tao

[MichalSimota]

Hi Jimmy Tao

Not sure, if I can add pbix file to make it easier, but expected result is in my original post(second table).

Maybe I can compare it with something else, but don't know how to build it.

I have there hour of day, because what I need as result in report is to see total of downtimes in each hour e.g.:

hour of day= 1 - 500s downtime

hour of day= 2 - 3600s downtime ....

Regards

Michal