Break the duration of status in hourly time frame

[dading]

Hi..

I'm new user in power bi. Hope someone can help me 😉

i have table that contains Status and time start, time end.

From that table i can calculate the duration_hours of each status.

My question is "How can we break the duration in certain time, in this case i want to break it in hourly?"

data_source

Timestamp_Start	Timestamp_End	Duration_hours	Status
13/03/2020 7:28	13/03/2020 8:30	1.03	A
13/03/2020 8:30	13/03/2020 9:15	0.74	B
13/03/2020 9:15	13/03/2020 10:37	1.37	A
13/03/2020 10:37	13/03/2020 10:50	0.22	C
13/03/2020 10:50	13/03/2020 11:05	0.25	B
13/03/2020 11:05	13/03/2020 11:28	0.38	C
13/03/2020 11:28	13/03/2020 11:50	0.37	A
13/03/2020 11:50	13/03/2020 12:10	0.33	B
13/03/2020 12:10	13/03/2020 13:10	1.00	C

the result i want is like table below

TimeFrame_Start	TimeFrame_End	Duration_hrs_A	Duration_hrs_B	Duration_hrs_C
13/03/2020 7:00	13/03/2020 8:00	0.52
13/03/2020 8:00	13/03/2020 9:00	0.51	0.49
13/03/2020 9:00	13/03/2020 10:00	0.75	0.25
13/03/2020 10:00	13/03/2020 11:00	0.62	0.17	0.22
13/03/2020 11:00	13/03/2020 12:00	0.37	0.25	0.38
13/03/2020 12:00	13/03/2020 13:00		0.17	0.83
13/03/2020 13:00	13/03/2020 14:00			0.17

the total duration should be same 5.69 hours

thanks in advance

[Greg_Deckler]

Are you trying to do something like Hour Breakdown? https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306

Sorry, not 100% clear.

[dading]

Hi @Greg_Deckler ,

yup..thats it

but i want the result until second level,

so which part should i modify? 

[v-juanli-msft]

Hi @dading 

It seems your hours are divided by the time intervals.

But i can't detemine how to spilt the hours, could you help to define the rule?

Best Regards

Maggie

[dading]

sure..

i save the excel file in google drive

link 

can you open it? @v-juanli-msft 

[v-juanli-msft · Accepted solution]

Hi @dading 

Create a new query in edit queries,

Close&&apply, create measures

hours_A =
VAR value_a =
    CALCULATE (
        SUM ( 'Table'[Duration_hours] ),
        FILTER (
            'Table',
            'Table'[Timestamp_Start]
                <= MAX ( 'Datetime list'[DateTime2] )
                && 'Table'[Timestamp_End]
                    >= MAX ( 'Datetime list'[DateTime] )
                && 'Table'[Status] = "A"
        )
    )
VAR mins =
    CALCULATE (
        MIN ( 'Table'[Timestamp_Start] ),
        FILTER (
            'Table',
            'Table'[Timestamp_Start]
                <= MAX ( 'Datetime list'[DateTime2] )
                && 'Table'[Timestamp_End]
                    >= MAX ( 'Datetime list'[DateTime] )
                && 'Table'[Status] = "A"
        )
    )
VAR maxe =
    CALCULATE (
        MAX ( 'Table'[Timestamp_End] ),
        FILTER (
            'Table',
            'Table'[Timestamp_Start]
                <= MAX ( 'Datetime list'[DateTime2] )
                && 'Table'[Timestamp_End]
                    >= MAX ( 'Datetime list'[DateTime] )
                && 'Table'[Status] = "A"
        )
    )
VAR h =
    IF (
        MAX ( 'Datetime list'[DateTime] ) <= mins,
        IF (
            MAX ( 'Datetime list'[DateTime2] ) <= maxe,
            DATEDIFF (
                mins,
                MAX ( 'Datetime list'[DateTime2] ),
                MINUTE
            ),
            DATEDIFF (
                mins,
                maxe,
                MINUTE
            )
        ),
        DATEDIFF (
            MAX ( 'Datetime list'[DateTime] ),
            maxe,
            MINUTE
        )
    )
VAR p =
    h
        / DATEDIFF (
            mins,
            maxe,
            MINUTE
        )
RETURN
    p * value_a

For [hours_b] and [hours_c] change the "A" in the measure above to "B" or "C".

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