cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichalSimota Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

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

5 REPLIES 5
v-cherch-msft Super Contributor
Super Contributor

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

MichalSimota Frequent Visitor
Frequent Visitor

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

MichalSimota Frequent Visitor
Frequent Visitor

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
MichalSimota Frequent Visitor
Frequent Visitor

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

v-cherch-msft Super Contributor
Super Contributor

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 122 members 1,535 guests
Please welcome our newest community members: