How to find average time lost between two tables of dates

[xmunchedx]

Hi there,

I'm totally new to Power BI but have been tasked with finding the average time lost between two points of time in a project (ie: time the project began versus when we finished.) It is however a list of projects so there are a lot of dates. I also have to find these averages for three specific time periods to be displayed as a % independent of one another. 

The tables have 4 columns: Year, Quater, Month, Day. I've been trying to figure out how to do this for so long and just can't get it so any help is appreciated! 

[Anonymous · Accepted solution]

Your solution is so great bhanu_gautam

Hi, @xmunchedx 

Based on your description, I used the following example dataset:

If your start date and end date are in two tables and exist in the form of year, month, and date, you need to create a column in each of the two tables:

StartDate
EndDate

startdate1 = DATE('Table'[Year],'Table'[Month],'Table'[Day])

End date = DATE('Table2'[Year],'Table2'[Month],'Table2'[Day])

Next, get the end date in table1:

Enddate1 = 
VAR _project_id = 'Table'[ProjectID]
RETURN 
CALCULATE(MAX('Table2'[End date]),'Table2'[ProjectID]=_project_id)

Then use the following processing method for the following two columns to find the average and percentage.

First, use the following expression to create a calculated column that will be used to find the number of days of lost time from the start to the end of the project:

time lost = DATEDIFF('Table'[StartDate],'Table'[EndDate],DAY)

Next, create the following three measures:

Average time lost = AVERAGE('Table'[time lost]) 

Total Time Lost = CALCULATE(SUM('Table'[time lost]),ALL('Table'))

Percentage = DIVIDE([Average time lost],[Total Time Lost])

Finally, in the Table visual, the result is as follows:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xmunchedx]

When I try the very first formula you gave me, it tells me I cannot convery value "November" of type Text to type integer even though the original column of dates has no text or "November" as its in mm/dd/yyyy form.

[bhanu_gautam]

@xmunchedx 

Create a new column in each table that combines the Year, Quarter, Month, and Day into a single date column. You can do this using the DATE function in Power BI's Data View.

For example, if your table is named StartDates and has columns Year, Quarter, Month, and Day, you can create a new column called StartDate:

StartDate = DATE(StartDates[Year], StartDates[Month], StartDates[Day])
Do the same for the end dates table:

EndDate = DATE(EndDates[Year], EndDates[Month], EndDates[Day])

Merge Tables: Merge the two tables based on a common key to have both start and end dates in a single table. You can do this in Power Query Editor.

Select the common key column in both tables and choose the type of join .

Calculate Time Lost: Create a new column in the merged table to calculate the time lost between the start and end dates. You can use the DATEDIFF function to calculate the difference in days, months, or years.

TimeLost = DATEDIFF(MergedTable[StartDate], MergedTable[EndDate], DAY)

Calculate Average Time Lost: Create a measure to calculate the average time lost.

AverageTimeLost = AVERAGE(MergedTable[TimeLost])

To calculate the averages for specific time periods (e.g., by Year, Quarter, Month), you can create additional measures or use slicers/filters in your report.

For example, to calculate the average time lost for a specific year:

AverageTimeLostByYear = CALCULATE(
AVERAGE(MergedTable[TimeLost]),
MergedTable[Year] = SELECTEDVALUE(MergedTable[Year])
)