Event Data - Only Include Total Seconds That Fall Within Selected Period

[GraemeSMiller]

New to Power BI and trying to learn - building a proof of concept about event data.

I have events with various data - each event has a start date/time and an end date/timeand we calculate the total seconds for that event. I can do great aggregation and total calculating for all events or events that start or end within a certain date range (using slicers)

However I wish to select a start and end date (assume with a slicer) and then alter the total time of an event based on how much of the the event falls within a selected period.

Any part of an event outwith the sliced period should not be included in total seconds of that event. E.g. For example say an event starts on 1st Feb and goes to 1st July. If the selected date range was 1st Jan to 1st March then I want to only include the time between 1st Feb and 1st Mar

If an event falls totally outwith a period it would have a total seconds of zero.

Any idea how to acheive this?

Thanks

Graeme

[GraemeSMiller · Accepted solution]

It is possible the general idea is use a date table to slice on. Then have calculated measure

Filtered Duration =
CALCULATE (
    SUMX (
        Event,
        DATEDIFF (
            MAX ( MIN ( 'Date'[Date] ), Event[StartDateTime] ),
            MIN ( MAX ( 'Date'[Date] ), Event[EndDateTime] ),
            SECOND
        )
    ),
    FILTER (
        'Event',
        'Event'[StartDateTime] <= MAX ( 'Date'[Date] )
            && 'Event'[EndDateTime] >= MIN ( 'Date'[Date] )
    )
)