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