Help Needed with DAX Measure for Average Completion Time in Power BI

[HamidBee]

Hi,

I'm working on a Power BI project and trying to create a KPI that calculates the average completion time between two dates using DAX. My data table has two columns: job_entry_date and actual_comp_date, and I want to find the average number of days it takes to complete a job.

Here is a sample data:

job_entry_date	actual_comp_date
26/07/2023 16:14	01/08/2023 14:26
25/07/2023 17:38	01/08/2023 16:26
27/07/2023 16:40	01/08/2023 16:29
18/07/2023 19:58	01/08/2023 16:30

I've attempted to write the following measure, but I'm getting an error:

Average Completion Time = 
AVERAGEX(
    'Table', 
    DATEDIFF('Table'[job_entry_date], 'Table'[actual_comp_date], DAY)
)

Can someone help me understand what's wrong with this measure? I'm not sure if my syntax is incorrect or if there's an issue with how I'm using the DATEDIFF function within the AVERAGE function.

I'd appreciate any guidance or advice on how to correctly write this measure.

Thank you!

[Ahmedx · Accepted solution]

you can create a calculated column
ColumnsDif = DATEDIFF('Table'[job_entry_date], 'Table'[actual_comp_date], DAY)
and only then calculate the average
Average Completion Time = AVERAGE('Table'[ColumnsDif])