cancel
Showing results 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',
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!
2 REPLIES 2
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Announcements

#### 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
Top Kudoed Authors