Forum Discussion

michaelshparber's avatar
michaelshparber
Advocate V
7 years ago
Solved

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
CaseIdCreatedDateClosedDate
12301/01/201830/05/2018
45621/01/201814/06/2018
78910/02/2018 
95102/03/201814/07/2018
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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's avatar
      michaelshparber
      Advocate V
      7 years ago

      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