Calculate average duration of contracts over time

[swestendorp]

Good day, 

I am looking for help on the following. I would like to measure the average duration of contracts as a trend over time. I have a table with contract.employee_number, contract.startdate and contract.enddate and a date table with a relationship between contract.startdate and date table.date.

I would like to know, for example, what the average duration of contracts was in June '15. We have two types of situation to average: contracts that have ended and contracts that are ongoing in June 15. The first is easily covered, but how do I include the ongoing contracts if I want to make their duration startdate to June 15? And how do I add that to my average with the ended contracts?

Thanks a lot for your help!

Sifra

[Greg_Deckler]

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[swestendorp]

Hi all,

Sure!

So I have two tables

1. Crew contract table

2. Date table

Crew contract table looks as follows. For running contracts I use today's date as end date. 

EmployeeNo	Actual first date	Actual end date
00443	20-4-2015	4-9-2017
00451	16-1-2017	4-9-2018
00460	4-6-2013	4-6-2016
00466	15-5-2015	7-9-2015
00492	11-5-2015	12-1-2008
00590	1-8-2018	4-9-2018

Data model looks as follows:

I need to determine what the average duration of ended and running contracts was at any given time.

What i would like to see is a time graph with year/month on X-axis and average duration of contracts on Y-axis. 

Thanks a lot!

[v-lili6-msft]

hi,@swestendorp

    you may try to refer to this article: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

If it is not your case, please share your expected output for data sample you provided it above.

Best Regards,

Lin

[swestendorp]

Hi Lin, 

Thanks a lot for reaching out, much appreciated. This is probably going waaay beyond my skill level, so my apologies in advance if I ask stupid questions. I think this could be a good solution to my issue, but I'm still struggling with the suggested measure. 

I've tried to adjust the measure to my situation:

Average duration = 
VAR tmpCalendar = ADDCOLUMNS('Date table',"Month1",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpContract = ADDCOLUMNS('Crew overview table',"MonthYearBegin",VALUE(YEAR([Actual first date]) & FORMAT(MONTH([Actual first date]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([Actual end date]) & FORMAT(MONTH([Actual end date]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpContract,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Employee",[EmployeeNo],
    "Year",[Year],
    "Month1",[Month],
    "ContractDuration",[Contract duration]
)
RETURN AverageX(tmpTable,[ContractDuration])

I now tried to plot it against my date table, but I get an error 'cannot convert value " of type Text to type Number'.

What am I doing wrong?