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

Reply
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:

CustomerCycle TimeBeginTaskEndTask
Apple1-212
Apple2-323
Apple3-434
Apple4-545
Apple5-656
Apple6-767
Orange1-212
Orange2-323
Orange3-434
Orange4-545
Orange5-656
Orange6-767

 

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:

IDProject#Project Task#DescProject Completed/Project ForecastDate Results Per TaskData Using (N/A or Blank Cycle Removed)
AB1X12341Beginning of ProjectProject Completed1/1/2019   
AB2X12342Material ReceivedProject CompletedN/A #VALUE! 
AB3X12343Material InstallProject Completed1/1/2019 #VALUE! 
AB4X12344InspectionProject Completed1/23/2019 2222
AB5X12345ValidationProject Completed2/4/2019 1212
AB6X12346ApprovedProject Completed6/4/2019 120120
AB7X12347End of ProjectProject Completed12/23/2019 202202
AB1X12341Beginning of ProjectProject Forecast1/2/2019   
AB2X12342Material ReceivedProject Forecast  -43467 
AB3X12343Material InstallProject Forecast1/5/2019 43470 
AB4X12344InspectionProject Forecast1/15/2019 1010
AB5X12345ValidationProject Forecast2/4/2019 2020
AB6X12346ApprovedProject Forecast6/10/2019 126126
AB7X12347End of ProjectProject Forecast12/21/2019 194194
AC1X12351Beginning of ProjectProject Completed1/2/2019   
AC2X12352Material ReceivedProject Completed  -43467 
AC3X12353Material InstallProject Completed1/5/2019 43470 
AC4X12354InspectionProject Completed1/24/2019 1919
AC5X12355ValidationProject Completed  -43489 
AC6X12356ApprovedProject Completed  00
AC7X12357End of ProjectProject Completed12/24/2019 43823 
AC1X12351Beginning of ProjectProject Forecast1/3/2019   
AC2X12352Material ReceivedProject Forecast  -43468 
AC3X12353Material InstallProject Forecast1/6/2019 43471 
AC4X12354InspectionProject Forecast1/16/2019 1010
AC5X12355ValidationProject Forecast2/5/2019 2020
AC6X12356ApprovedProject Forecast  -43501 
AC7X12357End of ProjectProject 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 TypeCycle TimeAverage
Project Completed1-2 
Project Completed2-3 
Project Completed3-421
Project Completed4-512
Project Completed5-660
Project Completed6-7202
Project Forecast1-2 
Project Forecast2-3 
Project Forecast3-420
Project Forecast4-540
Project Forecast5-6126
Project Forecast6-7194

 

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#DescProject Completed/Project ForecastProject Beg Task DateProject End Task Date
1-2Beginning of Project-Material ReceivedProject Completed1/1/2019N/A
2-3Material Received-Material InstallProject CompletedN/A1/1/2019
3-4Material Install-InspectionProject Completed1/1/20191/23/2019
4-5Inspection-ValidationProject Completed1/23/20192/4/2019
5-6Validation-ApprovedProject Completed2/4/20196/4/2019
6-7Approved-End of ProjectProject Completed6/4/201912/23/2019

 

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

3 REPLIES 3
sturlaws
Resident Rockstar
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

 

 

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 Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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