Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
2 years ago
Solved

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

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_dateactual_comp_date
26/07/2023 16:1401/08/2023 14:26
25/07/2023 17:3801/08/2023 16:26
27/07/2023 16:4001/08/2023 16:29
18/07/2023 19:5801/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!

  • 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])

1 Reply

  • 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])