Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Cycle Time Calculation

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..

3 REPLIES 3
Resident Rockstar

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
Not applicable

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

Resident Rockstar

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors