History Capture

Hi avanti295 ,

I suggest you to create an unrelated calendar table to help your calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'Table'[Start date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'Table'[End date] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "MM-YY", FORMAT ( [Date], "MMM-YY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Measure:

Open count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)

Closed count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))

Avg Open duration = 
CALCULATE (
    AVERAGE ( 'Table'[Days aging] ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)

Avg Closed duration = CALCULATE(AVERAGE('Table'[Days aging]),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))

 Result is as below.

You can download the attatch file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.