line chart visualizing a day in 24 hour buckets on X axis and total minutes on Y axis

[Bodo]

Hi all,

I want to create a line chart that divides a day into 24 hour buckets on the X axis and shows the total duration of events in minutes on the Y axis.

For this I have the following tables:
    a Bridge table with Area_ID, Event_ID
    an Events table with Event_ID, Start_Time, End_Time, Type
    a Raster table with Interval_Start, Interval_End, Hour

The Line Chart has to be filterable later via Area_ID, Type.
In SQL I would probably create a new table based on the event table, which would calculate the sum of the real duration of the events per bucket. Like this:

select
      INTERVAL_START
    , sum(event_duration) as total_event_duration
from
(
SELECT
	  raster.INTERVAL_START
	, rester.INTERVAL_END
	, event.event_ID
	, round( ( (case when raster.INTERVAL_END   < event.end_time   then raster.INTERVAL_END   else event.end_time end)
             - (case when raster.INTERVAL_START > event.start_time then raster.INTERVAL_START else task.start_time end) 
             )*1440 ) AS EVENT_DURATION
FROM
	  raster 
	, event
WHERE
	    event.START_TIME(+) < = raster.INTERVAL_END
	AND event.TASK.END_TIME(+) >= raster.INTERVAL_START)
)
group by 
    INTERVAL_START
order by
    INTERVAL_START

Given tables

  

that table that need to be created

Does anyone have an idea how to best implement this with Power BI?

[jennratten]

Hello - I have attached an example of how you can do this in Power BI.

[Bodo]

Hi @jennratten;,

Thank you so much for what you've done so far, it's awesome!

Unfortunately, the values are not quite right yet. For a line diagram you don't need the total event duration (i.e. end time - start time), but only the duration that an event actually has within a certain bucket.

In the example data event 4 is from 02:15-03:30.
So the event has 45 minutes in hour 2 and 30 minutes in hour 3, but in your example the event has only 15 minutes in the 2 hour and no minutes in the 3 hour.

Any ideas to fix that?