cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Frequent Visitor

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] )
)
)```
Frequent Visitor

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] )
)
)```

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors