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

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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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