Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GraemeSMiller
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
GraemeSMiller
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] )
    )
)

View solution in original post

1 REPLY 1
GraemeSMiller
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] )
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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