Dynamic average calculation with disconnected time dimension

[michaelshparber]

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

[MFelix · Accepted solution]

Hi @michaelshparber ,

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

[michaelshparber]

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