Skip to main content
cancel
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.

Reply
XeniaLi
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!
Rolling.PNG
2 REPLIES 2
Greg_Deckler
Super User
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

 

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors