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: 1,357
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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 1,357
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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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: 1,357
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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.