How to calculate the date difference between a date column and a filter expression

Question

I have a Fact table of cases with two date columns: startDate and endDate and a unitCount. I have an unrelated date dimension containing all dates.

This way I can calculate the number of cases as:

Number of cases (Beginning of month) :=CALCULATE(
sum('Fact'[unitCount]),
FILTER('Fact',
'Fact'[endDate]>=MIN(Date[Date]) &&
'Fact'[startDate]<=MIN(Date[Date])
))

This Works great producing a nice list:

Year-Month Number of cases(Beginning of month)
2018-01          2
2018-02          5
2018-03          3
... etc

I would like to add a column with a measure of the average duration of the cases each beginning of month.

I have tried making a measure:

Average duration (Beginning of month):=calculate(AVERAGEX('Fact',
datediff('Fact'[startDate],
MIN('Date'[Date]),
MONTH)),
FILTER('Fact',
'Fact'[endDate]>=MIN('Date'[Date]) &&
'Fact'[startDate]<=MIN('Date'[Date])
))

However this code fails with an error: In Datediff function The Start Date cannot be grater then the end date. This shouldn't be a problem since the filter prevents this from happening. How can I fix this?

Answer 1

I think I may have found the solution myself:

Average duration :=
CALCULATE (
    AVERAGEX (
        'Fact',
        DATEDIFF (
            'Fact'[startDate],
            MINX ( FILTER ( 'Date', Date[Date] >= 'Fact'[startDate] ), 'Date'[Date] ),
            MONTH
        )
    ),
    FILTER (
        'Fact',
        'Fact'[endDate] >= MIN ( Date[Date] )
            && 'Fact'[StartDate] <= MIN ( Date[Date] )
    )
)

This code seems to work well. Great!