Calculate duration between two dates

Questions & Answers
1

Hi all,

I’m looking to get a duration (which will be somewhere between a few minutes and a few hours) between issue creation and first transition to a status. I looked at this thread and this thread and came up with the following:

NonZero(AVG(
Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    -- filter only resolved issues in period
    DateInPeriod(
    [Issue].CurrentHierarchyMember.Get("Resolved at"),
    [Time].CurrentHierarchyMember
    )),
    CASE WHEN  
    [Measures].[Issues resolved] > 0
    THEN
    DateDiffDays(
    [Measures].[Issue created date],
    (
        [Measures].[Transition to status first date],
        [Transition Status].[Waiting for customer],
        [Time].CurrentHierarchy.DefaultMember
    )
    )
    END
))

This seems to work if I wanted decimal of days, but this value will mostly be sub 1 hour, so I’d prefer to get it as a Duration and format it appropriately.

Can anyone point me in the right direction?

2

Hi @MattDB ,

The input for the duration format should be the smallest unit. For example, if you use the formatting ##h ##m, you should return the result in minutes. The DateDiffDays actually does this with several decimals digits, so all you need to do is multiply the result to get it in minutes:

NonZero(AVG(
Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    -- filter only resolved issues in period
    DateInPeriod(
    [Issue].CurrentHierarchyMember.Get("Resolved at"),
    [Time].CurrentHierarchyMember
    )),
    CASE WHEN  
    [Measures].[Issues resolved] > 0
    THEN
    DateDiffDays(
    [Measures].[Issue created date],
    (
        [Measures].[Transition to status first date],
        [Transition Status].[Waiting for customer],
        [Time].CurrentHierarchy.DefaultMember
    )
    ) * 24 * 60
    END
))

Lauma / support@eazybi.com

3

I’m looking for something similar but I need the average time between the first transition to the status Escalated, and resolution.
I’ve created something with MDX, but the report times out.
Can I do something similar with Javascript or at least populate and “Escalated Date”?
Thank you,
Kat J

4

Hi @Kat_Jansen,

Yes, the calculation would be similar, but switching the dates around. Did you write it in some other way? I would be interested to see it and know more about the report configuration to fine-tune it. Probably you can send the definition over to support@eazybi.com.

Meanwhile, yes, it would be possible to calculate either the date of entering the Escalated status (from changelog) or also the days between the escalated date and resolved date (for performance, this would give the most visible result) in the JavaScript.

Lauma / support@eazybi.com