Split duration by hour of day

[MichalSimota]

Hi

I need to create report where downtime duration will be splited by hour of day when it happened. Now I have those data:

start	end	duration[min]
11:30	11:50	20
11:55	12:15	20
12:30	15:30	180

but need something like this:

start	end	duration[min]	hour of day	duration in hour[min]
11:30	11:50	20	11	20
11:55	12:15	20	11	5
			12	15
12:30	15:30	180	12	30
			13	60
			14	60
			15	30

Many thanks

Michal

[v-cherch-msft · Accepted solution]

Hi @MichalSimota

You may create tables to get it. Below is the sample file for your reference.

Table = GENERATESERIES(1,24,1)

Final =
FILTER (
    CROSSJOIN ( SampleData, 'Table' ),
    HOUR ( SampleData[start] ) <= 'Table'[Hour of Day]
        && HOUR ( SampleData[end] ) >= 'Table'[Hour of Day]
)

Last, add the column with IF condition:

duration in hour =
IF (
    HOUR ( Final[start] ) = HOUR ( Final[end] ),
    MINUTE ( Final[end] ) - MINUTE ( Final[start] ),
    IF (
        HOUR ( Final[start] ) = Final[Hour of Day],
        60 - MINUTE ( Final[start] ),
        IF ( HOUR ( Final[end] ) = Final[Hour of Day], MINUTE ( Final[end] ), 60 )
    )
)

Regards,

Cherie

[MichalSimota]

Hi

I just found out, that whith that formula I have problem, when dowtime if from 23PM to 1AM. It will just filter such lines out. As I'm new in DAX, I don't know ho to build condition to make it working.

Mathematicaly I will do it like that and add it to original formula:

IF DAY(start)<>DAY(end) THEN HOUR(start)<=(hour of day) AND HOUR(end)>=(hour of day)

But in DAX syntax I don't know. So even small hint with syntax can help me.

Many thanks

Michal

[v-cherch-msft]

Hi @MichalSimota

I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,

Cherie

[MichalSimota]

Thank you, it helps a lot. Just can you please help me how I should create IF condition, when I want to have it more precise?

Because I have time for example start 11:50:28 - end 11:53:10. Now I have result Duration=3. And if I want it like Duration=2.7 can you help me how to build it?

Many thanks

Michal

[MichalSimota]

Because I need it more precise, I change it a little to calculate seconds. It may help to some one. But still it's based on @v-cherch-msft idea and I really thank you for this big help.

duration in hour = IF(HOUR(Final[start])=HOUR( Final[end]);

(MINUTE( Final[end]-Final[start]))*60+SECOND(Final[end]-Final[start]);

IF(HOUR(Final[start] ) = Final[Hour of Day];
3600-((MINUTE(Final[start])*60)+SECOND(Final[start]));

IF(HOUR(Final[end] ) = Final[Hour of Day];

(MINUTE(Final[end])*60)+SECOND(Final[end]);3600)))