Forum Discussion

Dirk's avatar
Dirk
Frequent Visitor
8 years ago
Solved

Calculate average duration based on a calendar

Hi all,

I try to calculate the average "duration of activity" during time.

 

TaskNrStartEndDuration
11/07/201814/07/201814
21/07/2018 22
32/07/20185/07/20184
410/07/201814/07/20185
525/07/2018  

 

I calculated the duration compared to today by next formula:

 

= Table.AddColumn(#"Type gewijzigd", "Duration", each if DateTime.Date(DateTime.LocalNow()) < [Start] then
 null
else
  Number.From(List.Min({DateTime.Date(DateTime.LocalNow()),[End]}) - [Start])+1)

 

The average activity compared to today is 11,3.

 

What i try to do is become a table where I compare the duration according to a calendar instead of only today.

This is table I created Manually:

DateAverage

1/07/20181,0
2/07/20181,7
3/07/20182,7
4/07/20183,7
5/07/20184,7
6/07/20185,3
7/07/20186,0
8/07/20186,7
9/07/20187,3
10/07/20186,3
11/07/20187,0
12/07/20187,8
13/07/20188,5
14/07/20189,3
15/07/20189,5
16/07/20189,8
17/07/201810,0
18/07/201810,3
19/07/201810,5
20/07/201810,8
21/07/201811,0
22/07/201811,3
23/07/201811,5
24/07/201811,8
25/07/20189,8
26/07/201810,2
27/07/201810,6
28/07/201811,0
29/07/201811,4
30/07/201811,8
31/07/201812,2

 

Could someone help me to create it automatically through Power BI?

Thanks!

Dirk

Need Help
  • Dirk's avatar
    Dirk
    8 years ago

    Hi v-danhe-msft,

     

    Thanks a lot for trying to help me!

     

    For each day I calculated according to this reasoning:

     

    each if DateTime.Date(Specific Day) < [Start] then
     null
    else
      Number.From(List.Min({DateTime.Date(Specific Day),[End]}) - [Start])+1)

     

    and the I took the average for that Specific Day.

     

    In the meantime I have found a solution.

     

    For both tables (activities and Calendar) I added a Column with the number 1 in each Row.

     

     

    Then I merge the two tables with Column "Nr" as connection.

     

    With the new merged table I calculate the Duration with "Date" as reference.

    Merged Table with Calculation of duration

    Finally I group this table by "Date" and I take the Average of "Duration".

    This is how I obtain my expected table :-)

     

    Regards,

    Dirk

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi Dirk

    From your description, I could not figure out the data you posted in the ‘DataAverage’ table? Could you offer me more information about how you calculate the average activity?

    Regards,

    Daniel He

    • Dirk's avatar
      Dirk
      Frequent Visitor
      8 years ago

      Hi v-danhe-msft,

       

      Thanks a lot for trying to help me!

       

      For each day I calculated according to this reasoning:

       

      each if DateTime.Date(Specific Day) < [Start] then
       null
      else
        Number.From(List.Min({DateTime.Date(Specific Day),[End]}) - [Start])+1)

       

      and the I took the average for that Specific Day.

       

      In the meantime I have found a solution.

       

      For both tables (activities and Calendar) I added a Column with the number 1 in each Row.

       

       

      Then I merge the two tables with Column "Nr" as connection.

       

      With the new merged table I calculate the Duration with "Date" as reference.

      Merged Table with Calculation of duration

      Finally I group this table by "Date" and I take the Average of "Duration".

      This is how I obtain my expected table :-)

       

      Regards,

      Dirk