cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelshparber Regular Visitor
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
CaseIdCreatedDateClosedDate
12301/01/201830/05/2018
45621/01/201814/06/2018
78910/02/2018 
95102/03/201814/07/2018
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic average calculation with disconnected time dimension

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



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

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Dynamic average calculation with disconnected time dimension

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



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

Proud to be a Datanaut!




michaelshparber Regular Visitor
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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 357 members 3,673 guests
Please welcome our newest community members: