Reply
Frequent Visitor
Posts: 9
Registered: ‎12-03-2018
Accepted Solution

Split duration by hour of day

Hi

 

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

startendduration[min]
11:3011:5020
11:5512:1520
12:3015:30180

 

but need something like this:

startendduration[min]hour of dayduration in hour[min]
11:3011:50201120
11:5512:1520115
   1215
12:3015:301801230
   1360
   1460
   1530

 

Many thanks

Michal


Accepted Solutions
Community Support Team
Posts: 915
Registered: ‎07-25-2018

Re: Split duration by hour of day

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 )
    )
)

1.png

 

Regards,

Cherie

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 915
Registered: ‎07-25-2018

Re: Split duration by hour of day

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 )
    )
)

1.png

 

Regards,

Cherie

Attachment
Frequent Visitor
Posts: 9
Registered: ‎12-03-2018

Re: Split duration by hour of day

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

Frequent Visitor
Posts: 9
Registered: ‎12-03-2018

Re: Split duration by hour of day

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)))

Highlighted
Frequent Visitor
Posts: 9
Registered: ‎12-03-2018

Re: Split duration by hour of day

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

Community Support Team
Posts: 915
Registered: ‎07-25-2018

Re: Split duration by hour of day

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