Rolling duration for the open cases

[XeniaLi]

Hi guys, I really need your brilliant minds help.

I need to create a Average rolling duration calcuation for each open case. I had  a table with Referrall Key, Open Date and Close date (in Referral dimesnion connected to Fact Table), DimDate (Connected to Fact Table).

Below is the stage I'm currently in. Not sure how can I add Average and remove filter for porper calculation.

Average Legth Temp = VAR CurrentValue = MAX('Date'[Date])

                    VAR TestMAXDate = CALCULATE(

                                                MAXX('Fact Events Vertical',                                    

                                                RELATED('Date'[Date])),

                                                'Fact Events Vertical'[EventType] = "Referral",

                                                ALL('Date'),'Date'[Date] <= CurrentValue, 

                                ISBLANK(Referral[ReferralCloseDateTime])

                                || Referral[ReferralCloseDateTime]>=CurrentValue

                                            )

                    VAR EndValue = IF(ISBLANK(MAX(Referral[ReferralCloseDateTime])), CurrentValue, MAX(Referral[ReferralDischargeDateTime]))

                    VAR CountDurationTest = CALCULATE(AVERAGEX('Fact Events Vertical', DATEDIFF(TestMAXDate, EndValue, DAY)), ALL('Date'),'Date'[Date] <= CurrentValue, 

                                ISBLANK(Referral[ReferralDischargeDateTime])

                                || Referral[ReferralDischargeDateTime]>=CurrentValue)

 RETURN CountDurationTest

On the photo  highlighted number for January is wrong, as it pickes up Maximum Referral Date (i think 24/01/2022) and calculates date difference.
Hilighted 1 , is not a day it is ReferralKey, that number is correct. 
Many thanks for you help!

[Greg_Deckler]

@XeniaLi First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[XeniaLi]

Thank you for coming back to me @Greg_Deckler  

I'm actually more successful with sum of events. It is the duration (dated didfference) which creates challenge. We have events/investigations that can last months/ years, it means events might have end date, or can be open without date for forseable future. For example if same event open in March, April and May, the duration should rolled up. One more part of challenge is that Date table is not directly connected to the table which contains Date, it connects to long Fact tabewl with differnet type of events, some of events have close date (present in event type Dimension Table), some not supposed to have it.