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?
Oct 22, 2018 in Power BI 1,730 views

## 1 answer to this question.

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] )
)
)```
• 18,570 points

## create interactive R plots in Power BI

Right now Power BI only supports render ...READ MORE

## Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

## Combine tables in Power BI

You can also achieve this using a ...READ MORE

## Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

## Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

## Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE