Cycle Time Calculation

[Anonymous]

I need some assistance in pulling the cycle times for each task for each project and taking the average for each task where the task is the same in another project under the same customer.

We have customers that may have from 20 to 1000 projects per customer. Each of these projects can have from 1 to 50 tasks to complete. We track both the forecast and the completion date. If the task was completed a date is enter in the Date column, same for forecast.  We do have records where the dates are N/A for some task, because it may not be applicable for a project or they are blank because it has not been completed. In some case it is missed.

I have been able to build something where I can pull the “overall” average only and it works for customer that have only small number of tasks. As soon as I start adding filters to show average by month in the matrix table. it fails or takes minutes to refresh. There about 2.5M records in my table.

Beginning Task =

CALCULATE(

   SELECTEDVALUE(RefTable1[BeginTask]))

Ending Task =

CALCULATE(

   SELECTEDVALUE(RefTable1[EndTask]))

RefTable1:

Customer	Cycle Time	BeginTask	EndTask
Apple	1-2	1	2
Apple	2-3	2	3
Apple	3-4	3	4
Apple	4-5	4	5
Apple	5-6	5	6
Apple	6-7	6	7
Orange	1-2	1	2
Orange	2-3	2	3
Orange	3-4	3	4
Orange	4-5	4	5
Orange	5-6	5	6
Orange	6-7	6	7

These only shows 2 projects with 7 task Each but like I mention there could be up to 50 or more tasks.

Average Time =

CALCULATE(

   AVERAGE(Table1[Date]),

       Table1[Project Completed/Project Forecast]=" Project Completed",

   FILTER(Table1[Project Task#]=[Beginning Task]))-

CALCULATE(

   AVERAGE(Table1[Date]),

       Table1[Project Completed/Project Forecast]=" Project Forecast",

   FILTER(Table1[Project Task#]=[Ending Task]))-

Table1:

ID	Project#	Project Task#	Desc	Project Completed/Project Forecast	Date		Results Per Task	Data Using (N/A or Blank Cycle Removed)
AB1	X1234	1	Beginning of Project	Project Completed	1/1/2019
AB2	X1234	2	Material Received	Project Completed	N/A		#VALUE!
AB3	X1234	3	Material Install	Project Completed	1/1/2019		#VALUE!
AB4	X1234	4	Inspection	Project Completed	1/23/2019		22	22
AB5	X1234	5	Validation	Project Completed	2/4/2019		12	12
AB6	X1234	6	Approved	Project Completed	6/4/2019		120	120
AB7	X1234	7	End of Project	Project Completed	12/23/2019		202	202
AB1	X1234	1	Beginning of Project	Project Forecast	1/2/2019
AB2	X1234	2	Material Received	Project Forecast			-43467
AB3	X1234	3	Material Install	Project Forecast	1/5/2019		43470
AB4	X1234	4	Inspection	Project Forecast	1/15/2019		10	10
AB5	X1234	5	Validation	Project Forecast	2/4/2019		20	20
AB6	X1234	6	Approved	Project Forecast	6/10/2019		126	126
AB7	X1234	7	End of Project	Project Forecast	12/21/2019		194	194
AC1	X1235	1	Beginning of Project	Project Completed	1/2/2019
AC2	X1235	2	Material Received	Project Completed			-43467
AC3	X1235	3	Material Install	Project Completed	1/5/2019		43470
AC4	X1235	4	Inspection	Project Completed	1/24/2019		19	19
AC5	X1235	5	Validation	Project Completed			-43489
AC6	X1235	6	Approved	Project Completed			0	0
AC7	X1235	7	End of Project	Project Completed	12/24/2019		43823
AC1	X1235	1	Beginning of Project	Project Forecast	1/3/2019
AC2	X1235	2	Material Received	Project Forecast			-43468
AC3	X1235	3	Material Install	Project Forecast	1/6/2019		43471
AC4	X1235	4	Inspection	Project Forecast	1/16/2019		10	10
AC5	X1235	5	Validation	Project Forecast	2/5/2019		20	20
AC6	X1235	6	Approved	Project Forecast			-43501
AC7	X1235	7	End of Project	Project Forecast

Results that i am able to get with current measures, but it will not allows me to analyst additionally. Also looking to be able to breakout additional task from begining to end date in the matrix table.  This is just overall data

Task Type	Cycle Time	Average
Project Completed	1-2
Project Completed	2-3
Project Completed	3-4	21
Project Completed	4-5	12
Project Completed	5-6	60
Project Completed	6-7	202
Project Forecast	1-2
Project Forecast	2-3
Project Forecast	3-4	20
Project Forecast	4-5	40
Project Forecast	5-6	126
Project Forecast	6-7	194

Also looking to be able to breakout additional task from begining to end date in the matrix table.  This is just overall data

Project Task#	Desc	Project Completed/Project Forecast	Project Beg Task Date	Project End Task Date
1-2	Beginning of Project-Material Received	Project Completed	1/1/2019	N/A
2-3	Material Received-Material Install	Project Completed	N/A	1/1/2019
3-4	Material Install-Inspection	Project Completed	1/1/2019	1/23/2019
4-5	Inspection-Validation	Project Completed	1/23/2019	2/4/2019
5-6	Validation-Approved	Project Completed	2/4/2019	6/4/2019
6-7	Approved-End of Project	Project Completed	6/4/2019	12/23/2019

any help or suggestions will be greatly appreciated...Thanks in Advance..

[sturlaws]

Hi @Anonymous,

this is a bit confusing to me. Could you create a sample report where you recreate your issues and share it?

Cheers,
Sturla

[Anonymous]

Hi,

Sorry was a bit slow to put it together, hopefully you can review still. As you can see i am able to produce most of what i need but when it is build with millions of records it times out.

Cycle Time Report Sample

[sturlaws]

That helps a little, now I know you data structure. 

I think there is something missing in your model. On the page "Data I am able to pull", in the bottom table visual, you have [Customer], [Cycle Time], [begin date value], [end date value] and [average time]. [begin date value], [end date value] and [average time] are measures calculated with values from 'Data'. But there is no relationship between the tables 'Data' and 'RefTable'. So in the mentioned table, for each combination of [Customer] and [Cycle time], the 3 measures are calculated over the entire 'Data' table. Which works fine when the table is small, and less fine when there is 2,5 million rows. In addition to being slow, it will also show you the wrong numbers, because [Customer] does not impact/filter the data when the measures are computed. You can see this in your table as well, all 3 measures show the same for the 2 customers.

To remedy this, and get the numbers you want, you need a customer reference in the 'Data' table.

Cheers,
Sturla