Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Jodallen123
Frequent Visitor

Total average of datediff

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.

 

Jodallen123_1-1695708705025.png

 

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! 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors