Calculate average duration based on a calendar

[Dirk]

Hi all,

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

TaskNr	Start	End	Duration
1	1/07/2018	14/07/2018	14
2	1/07/2018		22
3	2/07/2018	5/07/2018	4
4	10/07/2018	14/07/2018	5
5	25/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/2018	1,0
2/07/2018	1,7
3/07/2018	2,7
4/07/2018	3,7
5/07/2018	4,7
6/07/2018	5,3
7/07/2018	6,0
8/07/2018	6,7
9/07/2018	7,3
10/07/2018	6,3
11/07/2018	7,0
12/07/2018	7,8
13/07/2018	8,5
14/07/2018	9,3
15/07/2018	9,5
16/07/2018	9,8
17/07/2018	10,0
18/07/2018	10,3
19/07/2018	10,5
20/07/2018	10,8
21/07/2018	11,0
22/07/2018	11,3
23/07/2018	11,5
24/07/2018	11,8
25/07/2018	9,8
26/07/2018	10,2
27/07/2018	10,6
28/07/2018	11,0
29/07/2018	11,4
30/07/2018	11,8
31/07/2018	12,2

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

Thanks!

Dirk

[v-danhe-msft]

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 · Accepted solution]

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