cancel
Showing results for
Search instead for
Did you mean:
Highlighted
michaelshparber 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
Please help
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!

michaelshparber 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

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 40 members 913 guests
Recent signins:
• johankent30 • mnahmany • denyschamberlan • mtoralba_hfdc • jincysarah • Skittle0302 • Please welcome our newest community members:
• mtoralba_hfdc • dougshepard • Pmullins0713 • michalaq • navitram • ijivanjee • dharsanj 