Calculate average duration based on a calendar

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