Forum Discussion

GraemeSMiller's avatar
GraemeSMiller
Frequent Visitor
9 years ago
Solved

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

Need Help
  • GraemeSMiller's avatar
    GraemeSMiller
    9 years ago

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

1 Reply

  • GraemeSMiller's avatar
    GraemeSMiller
    Frequent Visitor
    9 years ago

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