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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.