Summarize According to selected time Interval

[andrezmor]

Hi there.  I am having some trouble finding a solution to the next problem. I appreciate your help and suggestions.

I have an Event Table with 3 columns as the following

I want to be able to select a time interval and summarize the number of hours per Event Class within the selected interval.

Start Time	End Time	Event Class
3/4/2021 20:00	3/6/2021 1:00	Class A
3/5/2021 16:00	3/6/2021 4:00	Class B
3/6/2021 20:00	3/7/2021 3:00	Class A
6/3/2021 2:30	6/3/2021 14:30	Class C
6/23/2021 14:10	6/24/2021 2:10	Class B
1/16/2021 10:30	1/16/2021 18:45	Class D
3/5/2021 21:00	3/8/2021 9:00	Class A
5/21/2021 20:00	5/22/2021 4:15	Class C
3/5/2021 13:00	3/6/2021 13:00	Class C

Selected Interval

3/5/2021 0:00

3/7/2021 0:00

Expected results

Event Class	Total Hours
Class A	56.0
Class B	12.0
Class C	36.0
Class D	0

Things to have in mind:

• The selected interval is only between days so user will not have the ability to select specific hours. I am open to your suggestion about what type of control I have to use. I guess a time slicer.
• If the event start end time is out of the range it will not be summarize
• If the event is partially within the selected interval, it will only add the number of hours that are within the interval.

I really have not been able to set up the time table relationships or the measures to get the results.

I appreciate your help.

[andrezmor]

Thank you very much @MFelix , I was not able to see the Hours_Total.pbix because my organization works with Power Bi Report Server and I got a version of incompatible versions. However I was able to replicate the measure in my Data Model and it worked perfect. Thank you very much for your help. 

[MFelix · Accepted solution]

Hi @andrezmor ,

First of all believe that you are counting 12 additional hours on the Class C according to your data the C values are has accordingly:

Start Time	End Time	Event Class
6/3/2021 2:30	6/3/2021 14:30	Class C
5/21/2021 20:00	5/22/2021 4:15	Class C
3/5/2021 13:00	3/6/2021 13:00	Class C

If your filter is based on the 3/5 and 3/7 only the third line is consider makingi it the 24 hours and not 36, I assume you are counting also the first 12 hours from the first line but it does not match the filter.

Try the following measure:

Total Hours = 
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALL ( 'Table'[Event Class], 'Table'[Start Time], 'Table'[End Time] ),
            'Table'[Event Class],
            'Table'[Start Time],
            'Table'[End Time],
            "@HourDifference",
                DATEDIFF (
                    MAX ( 'Table'[Start Time], MIN ( 'Calendar'[Date] ) ),
                    MIN ( 'Table'[End Time], MAX ( 'Calendar'[Date] ) ),
                    HOUR
                )
        ),
        [@HourDifference] > 0
            && 'Table'[Event Class] IN VALUES ( 'Table'[Event Class] )
    )
RETURN
    COALESCE ( SUMX ( temp_table, [@HourDifference] ), 0 )