Total average of datediff

[Jodallen123]

Hello,

I am trying to create a measure that calculates average handling time per project. My data looks something like this, it's a table with logs of status changes.

My current measure looks like this, when i drag it to a table with ordernumber, it gives the correct number of days per ordernumber, but the total at the bottom shows the total difference between the min and the max date, and not the total average per ordernumber. I know that the current measure won't show the average since i haven't included any averages in the measure, i have however tried to alter the measure in a bunch of ways, but the result is still the same. 

PL handling time =

VAR minbookedmeeting =

    CALCULATE (

        MIN ( 'logs'[Created_date] ),

        'logs'[status_changed_to] = "B"

            || logs'[status_changed_to] = "C"

    )

VAR maxcustomerdone =

    CALCULATE (

        MAX (  logs'[Created_date] ),

        'logs'[status_changed_to] = " D " || 'logs'[status_changed_to] = " E" )

VAR datedifference =

    DATEDIFF(  minbookedmeeting, maxcustomerdone, DAY )

RETURN

    datedifference

I would very much appreciate some input on how I can change the measure to show what I want, thanks in advance! 

[OwenAuger · Accepted solution]

Hi @Jodallen123 

Try this:

PL handling time =
AVERAGEX (
    VALUES ( 'logs'[Ordernumber] ),
    VAR minbookedmeeting =
        CALCULATE (
            MIN ( 'logs'[Created_date] ),
            'logs'[status_changed_to] = "B"
                || 'logs'[status_changed_to] = "C"
        )
    VAR maxcustomerdone =
        CALCULATE (
            MAX ( 'logs'[Created_date] ),
            'logs'[status_changed_to] = "D"
                || 'logs'[status_changed_to] = "E"
        )
    VAR datedifference = maxcustomerdone - minbookedmeeting
    RETURN
        datedifference
)

It's essentially the same code as you had, but wrapped in AVERAGEX ( VALUES (... ), ... )

AVERAGEX (
    VALUES ( 'logs'[Ordernumber] ),
    <your original code>
)

This will compute the datedifference for each Ordernumber, then return the arithmetic mean of these values.

For simplicity, I also suggest subtracting the dates rather than using DATEDIFF (they are both valid though).

Does this work for you?

Regards

[Jodallen123]

Thank you!! 

I tried the code just like you wrote it, which didn't give the correct values but when i tried using datediff instead of subtracting the dates, it does work. It is very possible that I am doing something else wrong when I am subtracting the values but I am just glad it works now!