cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Dynamic average calculation with disconnected time dimension

Hello friends,

I need to calculate an agerage age (in days) of all open customer cases - historically.

Below is the format of my table.

Until a case has a closed date - it is considered open.

I am struggling to calculate the average age with disconnected dimention.

For the first case for example - I need it to be 1 on 02-Jan-18, 2 on 03-Jan-18, 20 on 21-Jan-18, etc.

When the second case is also open - it should lower the average. So on 22-Jan-18 the calculation should be (21+1)/2 = 11 days.

I tried something like:

AVERAGEX('Case',MAX('Calendar'[Date])-'Case'[CreatedDate]) but no success
Thanks!
Michael
 CaseId CreatedDate ClosedDate 123 01/01/2018 30/05/2018 456 21/01/2018 14/06/2018 789 10/02/2018 951 02/03/2018 14/07/2018
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Dynamic average calculation with disconnected time dimension

Try the following measure:

```Average =
DIVIDE (
SUMX (
FILTER (
'Case';
'Case'[CreatedDate] <= MAX ( 'Calendar'[Date] )
&& (
'Case'[ClosedDate] >= MAX ( 'Calendar'[Date] )
|| 'Case'[ClosedDate] = BLANK ()
)
);
DATEDIFF ( 'Case'[CreatedDate]; MAX ( 'Calendar'[Date] ); DAY ) + 1
);
CALCULATE (
DISTINCTCOUNT ( 'Case'[CaseId] );
FILTER (
'Case';
'Case'[CreatedDate] <= MAX ( 'Calendar'[Date] )
&& (
'Case'[ClosedDate] >= MAX ( 'Calendar'[Date] )
|| 'Case'[ClosedDate] = BLANK ()
)
)
)
)```

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Dynamic average calculation with disconnected time dimension

Try the following measure:

```Average =
DIVIDE (
SUMX (
FILTER (
'Case';
'Case'[CreatedDate] <= MAX ( 'Calendar'[Date] )
&& (
'Case'[ClosedDate] >= MAX ( 'Calendar'[Date] )
|| 'Case'[ClosedDate] = BLANK ()
)
);
DATEDIFF ( 'Case'[CreatedDate]; MAX ( 'Calendar'[Date] ); DAY ) + 1
);
CALCULATE (
DISTINCTCOUNT ( 'Case'[CaseId] );
FILTER (
'Case';
'Case'[CreatedDate] <= MAX ( 'Calendar'[Date] )
&& (
'Case'[ClosedDate] >= MAX ( 'Calendar'[Date] )
|| 'Case'[ClosedDate] = BLANK ()
)
)
)
)```

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Dynamic average calculation with disconnected time dimension

Amazing! Thank you very much @MFelix !

It is working!

Later I'll try to make this work directly with AVERAGEX - I think it is also possible...

Thanks a lot for your quick help!

Have a great wekend!

Michael

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 40 members 913 guests
Recent signins: