Forum Discussion

bgepps's avatar
bgepps
Frequent Visitor
9 years ago
Solved

Create a column to calculate average time difference

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's avatar
    v-huizhn-msft
    9 years ago

    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

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      bgepps
      Frequent Visitor
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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