Create a column to calculate average time difference

[bgepps]

Hi guys,

I am struggling with a problem calculating average days/times in Power BI Desktop.

I have the following sets of data;

• Job status
• Status start date (date/hh/mm/ss)
• Status end date (date/hh/mm/ss)

My aim is to find a formula/s which will help me calculate;

• The time intervals spent between different job status'
• The average time spent on each job status

Does anyone know the steps, or the DAX formula's that i can use to calculate these sets of information? 

Thanks!

[v-huizhn-msft]

Hi @bgepps,

Based on your description, I try to create sample data and reproduce your scenario, I get the expected result as follows.

Create sample data.

 

Create calculate column to get the time intervals spent for each status.

Diff = DATEDIFF(Table5[Start Date],Table5[End Date],HOUR)

 

Create a measure to calculate the average time spent on each job status. Create table to show the result.

Average = DIVIDE(CALCULATE(SUM(Table5[Diff]),ALL(Table5)),CALCULATE(DISTINCTCOUNT(Table5[Status]),ALL(Table5[Status])))

 
If this is what you want, please share the sample data for further analysis.

Best Regards,
Angelia

[bgepps]

Hi Angelia,

Thanks for your help with this, it looks promising so far.

Once i have added the measure, how do i produce the table like your example below? Apologies, bit of a BI beginner!

Appreciate your help,

Ben

[v-huizhn-msft · Accepted solution]

Hi @bgepps,

Please see the screenshot below, click the table visualization, select the Status, Diff and Average measure as Values level. You will get the expected result.

Best Regards,
Angelia