Average for amount of days open on tasks

[TIGER8855]

I need to create a measure showing the average days open for tasks over the last 5 years at monthly intervals. The tasks can have a start date and an end date. At any given month, I need to measure how many days a task has been open and if a task has been closed, how long the task took and then add them all together and work out the average (if the start date is blank, this should be disregarded). 

Below is a table with sample data and then the last 2 columns show what I would expect the count to be on the 1st Jan of that year and then what the average should be.

ID	Start	End	1-Jan-18	1-Jan-19
24	2-Mar-17	3-Sep-20	305	670
27	26-Apr-17		250	615
21
5	22-Nov-17	28-Jan-20	40	395
10	5-Feb-18	8-Aug-22	198.33	330
4	22-Feb-18	29-Oct-20		313
6	14-Mar-18	16-Jan-22		293
25	18-Mar-18	18-Aug-20		289
3	28-Mar-18	8-May-18		41
11	11-Apr-18			265
34	8-May-18			238
9	30-May-18			216
13	15-Oct-18			78
21	22-Oct-18			71
33	22-Oct-18			71
37	8-Nov-18			54
17	12-Nov-18	1-Oct-119		50
31	14-Nov-18			48
18	21-Nov-18	24-Sep-20		41
12	19-Dec-18	23-Nov-19		13
				215.32

[TIGER8855]

Is anyone able to assist with my query?

[amitchandak]

@TIGER8855 , Open date using a date table not joined with task table

A measure like

Averagex(Values(Task[ID]) , Switch(True() ,

isblank(Max(Table[End Date])) , datediff(Min(Table[Start Date]), max(date[Date]) , day) ,

Max(Table[End Date]) <max(date[Date]), blank() ,

datediff(Min(Table[Start Date]), Min( max(date[Date]) , Max(Table[End Date]) ) , day)

)

[TIGER8855]

@amitchandak when using this measure, the output is only calculating the days open for the first entry (ID 24 which has the earliest start date).

So the value on the 1-Jan-18 is 305 and on the 1-Jan-19 it's 670.