Forum Discussion
Summarize According to selected time Interval
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.
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 )
2 Replies
- MFelix
Super User
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 ) 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.
