Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Rolling duration for the open cases

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',                                    
                                                'Fact Events Vertical'[EventType] = "Referral",
                                                ALL('Date'),'Date'[Date] <= CurrentValue, 
                                || 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, 
                                || 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!
Super User
Super User

@XeniaLi First, please vote for this idea:

This looks like a measure totals problem. Very common. See my post about it here:

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:



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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors