Calculation of contract duration with several start/end dates

[sdejong]

Hi all,

I have a question regarding time intelligence calculation.  I would like to calculate the average duration of a contract when the same contract has several start and end dates due to extension of the contract. How can I calculate the duration for contract id A1 (6 months) using the first start date and the latest end date? 

Contract id	Start date	End date	Duration in months
A1	01-01-2017	31-01-2017	1
A2	01-01-2017	30-06-2017	6
A1	01-02-2017	30-06-2017	5

Thanks in advance,

Best, 

Sara

[Eric_Zhang]

---

@sdejong wrote:

Hi all,

I have a question regarding time intelligence calculation.  I would like to calculate the average duration of a contract when the same contract has several start and end dates due to extension of the contract. How can I calculate the duration for contract id A1 (6 months) using the first start date and the latest end date? 

Contract id	Start date	End date	Duration in months
A1	01-01-2017	31-01-2017	1
A2	01-01-2017	30-06-2017	6
A1	01-02-2017	30-06-2017	5

Thanks in advance,

Best, 

Sara

---

@sdejong

What do you mean "calculate the average duration" while mentioning "using the first start date and latest end date"? In latter case, you would create a measure as 

month diff = MONTH(MAX(Table1[End date]))-MONTH(MIN(Table1[Start date]))+1

[Interkoubess]

Hi @sdejong,

What about entering a measure with Test = DATEDIFF(Data[Start date],Data[End date],MONTH)+1

see picture below and then with a matrix view you have a sum.

Please elaborate if it is not the solution to your request.

[sdejong]

Hi Interkoubess,

Thanks for the quick reply! I will check it out,

Best,

Sara