adhoc reporting in power bi

Question

I'm trying to do adhoc reporting but I'm stuck somewhere. So i have a start date and and end date and need to consider those dates to calculate total number of seconds and percentage. But the problem is sometimes the start date is considered before the start date and end date is after the actual end date. I want only the actual/original start and end date to be considered for the calculation. Is that possible?

Answer 1

Yse a DAX measure to calculate the sum of the duration for any events that fall on a given date. You can do this using a disconnected date table. 

For example, if you have a disconnected date table called Date, and your event table is called Event, you can have a measure such as:

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