Average age of outstanding job in period

[Langers1]

Hello,

Ive been using Power BI for the last 2 weeks so a I am fairly new at this, learnt a lot from the forums so thanks for all those that post solutions. I am struggling to find the solution though to calculate the average age of outstanding jobs up to a set period depending how the user drills down the date fields i.e. Year, Quater, Month, Day.

I.e. in January 2017 the average age of outstanding jobs was 50 days, March 2017 the average age of outstanding jobs was 48 days.

I have a table that has the Job Created Date (KPI_Job_Report [job_entry_date]) and the Job Completion Date (KPI_Job_Report[actual_comp_date]]), if the job is still open then 'KPI_Job_Report Aged'[actual_comp_date] is null.

The KPI_Job_Report table has a relationship with another table called Calender (Which is just a set table with calender dates in), this is joined using the KPI_Job_Report[actual_comp_date] and the calander table is used as the main filter and shared access in the dashboard. I am presuming for the report to work correctly i need to create a new table using the same data and link it to the Calander table using the actual_comp_date field (however in the case of where this is blank how do i take this into account, use an If function to create a new column so If [actual_comp_date] = blank(), Today(), [actual_comp_date]).

One other issue i have is that sometimes the Actual completion date is before the job raised date, this is also probably easy to fix so that the Datediff function can be used either by another if function or a filter where actual_comp_date<job_entry_date.

What i am really struggling with is the actual statement to pull all this together, ive been at it a week so far so if anyone can help me it would be greatly appreciated.

[Eric_Zhang]

---

@Langers1 wrote:

Hello,

Ive been using Power BI for the last 2 weeks so a I am fairly new at this, learnt a lot from the forums so thanks for all those that post solutions. I am struggling to find the solution though to calculate the average age of outstanding jobs up to a set period depending how the user drills down the date fields i.e. Year, Quater, Month, Day.

I.e. in January 2017 the average age of outstanding jobs was 50 days, March 2017 the average age of outstanding jobs was 48 days.

I have a table that has the Job Created Date (KPI_Job_Report [job_entry_date]) and the Job Completion Date (KPI_Job_Report[actual_comp_date]]), if the job is still open then 'KPI_Job_Report Aged'[actual_comp_date] is null.

The KPI_Job_Report table has a relationship with another table called Calender (Which is just a set table with calender dates in), this is joined using the KPI_Job_Report[actual_comp_date] and the calander table is used as the main filter and shared access in the dashboard. I am presuming for the report to work correctly i need to create a new table using the same data and link it to the Calander table using the actual_comp_date field (however in the case of where this is blank how do i take this into account, use an If function to create a new column so If [actual_comp_date] = blank(), Today(), [actual_comp_date]).

One other issue i have is that sometimes the Actual completion date is before the job raised date, this is also probably easy to fix so that the Datediff function can be used either by another if function or a filter where actual_comp_date<job_entry_date.

What i am really struggling with is the actual statement to pull all this together, ive been at it a week so far so if anyone can help me it would be greatly appreciated.

---

Roughly, you can create a calculated column

elaspedDays =
IF (
    ISBLANK ( [actual_comp_date] ),
    DATEDIFF ( [job_entry_date], TODAY (), DAY ),
    IF (
        [actual_comp_date] >= [job_entry_date],
        DATEDIFF ( [job_entry_date], [actual_comp_date], DAY ),
        -1//-1 is used as a filter to exclude the rows [actual_comp_date] < [job_entry_date]
    )
)

Then create a measure

average age of outstanding jobs =
AVERAGEX (
    FILTER ( KPI_Job_Report, KPI_Job_Report[elaspedDays] > -1 ),
    KPI_Job_Report[elaspedDays]
)

For further questions, it would be more helpful that you can upload a sample pbix to help understand more details about your scenario.

[Langers1]

Hi Eric,

Thanks very much for getting back to me its really appreciated, if i told you how many times i have layed in bed with a bit of paper scrawling out equations to resolve this haha. I have stripped out all the non essential stuff and uploaded the pbix file here with a stripped down data source (the originals were huge). 

Im trying to get PB to calculate the average age of outstanding defect for each period (based on the calander date shared access), i.e if it was raised in Jan, completed in April it still needs to calculate and display that on the graph how old it was in Feb, March etc (With the rest of the open jobs up to that period as an average), as it was completed in April it wont show up in April (Unless i drill down to days on the graph when it will show up right up to the day before it was completed). So I'm thinking the elaspedDays would need to calculate the Datediff([job_entry_date],Calander[Date]) if job_entry_date > Calander[Date] and actual_comp_date > Calander[Date], if actual_comp_date = null then return Today() for actual_comp_date.

Would the best way to do this involve creating a new calculated table and then sumarizing the duration the jobs were open using the above equation somehow?

Using the attached source file i am expecting the current average age of the outstanding jobs to be around 212 days.