topic Re: Total average of datediff in DAX Commands and Tips

Total average of datediff

Jodallen123 — Tue, 26 Sep 2023 06:20:52 GMT

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!

Re: Total average of datediff

OwenAuger — Tue, 26 Sep 2023 07:38:38 GMT

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

Re: Total average of datediff

Jodallen123 — Tue, 26 Sep 2023 08:26:01 GMT

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!