cancel
Showing results 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

Frequent Visitor

## Construct calculated column for time spent on "Work"

Hello everyone.

I am fairly new to Power BI and could use some assistance in creating a calculated column.

I wish to calculate the time spent from "Work" to "Review" for each project and paste the duration in a new column (Leadtime(days)). If multiple "Work" are present in the same project, the function should summarise the time spent from each "Work" to "Review".
If the latest Status is "Work", then i wish to add the time spent from the lastest "Work" to the current time of day. In my example for Project 3, the current time of day is 29-08-2022 11:00.

 Project Status Date Leadtime(days) 1 Work 20-08-2022 10:00 1,00 1 Review 21-08-2022 10:00 1,00 1 Done 22-08-2022 09:00 1,00 2 Work 21-08-2022 08:00 2,25 2 Review 22-08-2022 11:00 2,25 2 Work 23-08-2022 08:00 2,25 2 Review 24-08-2022 11:00 2,25 2 Done 25-08-2022 11:00 2,25 3 Work 24-08-2022 08:00 2,08 3 Review 25-08-2022 08:00 2,08 3 Work 28-08-2022 09:00 2,08

Any help will be much appreciated.

1 ACCEPTED SOLUTION
Super User

Hi @CBXS
Please refer to attached sample file with the solution.

``````Leadtime (Days) =
VAR T1 = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Project] ) )
VAR T2 = FILTER ( T1, Data[Status] = "Work" )
VAR T3 =
T2,
"@ReviewDate",
VAR CurrentDate = [Date]
VAR T4 = FILTER ( T1, [Date] > CurrentDate && Data[Status] = "Review" )
RETURN
COALESCE ( MINX ( T4, [Date] ), NOW ( ) )
)
VAR T5 = ADDCOLUMNS ( T3, "@LeadTime", DATEDIFF ( [Date], [@ReviewDate], HOUR ) )
RETURN
DIVIDE ( SUMX ( T5, [@LeadTime] ), 24 )``````
9 REPLIES 9
Super User

Hi @CBXS
Please refer to attached sample file with the solution.

``````Leadtime (Days) =
VAR T1 = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Project] ) )
VAR T2 = FILTER ( T1, Data[Status] = "Work" )
VAR T3 =
T2,
"@ReviewDate",
VAR CurrentDate = [Date]
VAR T4 = FILTER ( T1, [Date] > CurrentDate && Data[Status] = "Review" )
RETURN
COALESCE ( MINX ( T4, [Date] ), NOW ( ) )
)
VAR T5 = ADDCOLUMNS ( T3, "@LeadTime", DATEDIFF ( [Date], [@ReviewDate], HOUR ) )
RETURN
DIVIDE ( SUMX ( T5, [@LeadTime] ), 24 )``````
Frequent Visitor

Hi @tamerj1 .

My goal with the data has changed a bit and I was hoping you (or someone else) could lend me your expertise once again.

Now I would like to calculate the number of days spend on Work to Review, but I want to ignore the first Work Status and use LeadtimeStart instead. Furthermore I would like to ignore days in the weekend using the NETWORKDAYS function.

 Project Status Date LeadtimeStart Leadtime(days) 1 Work 20-08-2022 19-08-2022 2 1 Review 22-08-2022 19-08-2022 2 1 Done 22-08-2022 19-08-2022 2 2 Work 21-08-2022 19-08-2022 4 2 Review 22-08-2022 19-08-2022 4 2 Work 23-08-2022 19-08-2022 4 2 Review 24-08-2022 19-08-2022 4 2 Done 25-08-2022 19-08-2022 4 3 Work 24-08-2022 23-08-2022 7 3 Review 25-08-2022 23-08-2022 7 3 Work 29-08-2022 23-08-2022 7

The function should look something like this:

Leadtime(days)(ProjectX) = NETWORKDAYS([LeadtimeStart, Review1, 1] + 1 + NETWORKDAYS([Work2, Review2, 1] + 1 + ... (I add 1 to include the start date)

The leadtime for each project should like something like so:

Leadtime(days)(project1) = NETWORKDAYS( 19-08-2022, 22-08-2022, 1 ) + 1  = 2 ( 20/21-08-2022 are weekend days )

Leadtime(days)(project2) = NETWORKDAYS( 19-08-2022, 22-08-2022, 1 ) + 1 + NETWORKDAYS( 23-08-2022, 24-08-2022, 1 ) + 1 = 4

Leadtime(days(project3) = NETWORKDAYS( 23-08-2022, 25-08-2022, 1 ) + 1 + NETWORKDAYS( 29-08-2022, TODAY( ), 1 ) + 1 = 7 (assuming TODAY() is 01-09-2022)

I hope you are able to assist me and I would be most grateful if you did.

Regards, CBXS

Super User

Hi @CBXS
How many Work-Review Cycles a project could have?

Frequent Visitor

No more than 5 cycles a project. Most will have 1-2.

Super User

Hi @CBXS
Sorry for the late reply. The solution is a bit complex. Please refer to attached sample file

``````Leadtime(days) =
VAR T1 = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Project] ) )
VAR T2 =
GENERATE (
T1,
VAR CurrentDate = [Date]
VAR MaxDate = MAXX ( T1, [Date] )
VAR PrevWorkTable = FILTER ( T1, [Date] <= CurrentDate && [Status] = "Work" )
VAR RevCycleNum = COUNTROWS ( PrevWorkTable )
VAR PrevWorkDate = MAXX ( PrevWorkTable, [Date] )
VAR ReviewTable = FILTER ( T1, [Status] = "Review" )
VAR NextReviewTable = FILTER ( ReviewTable, [Date] >= CurrentDate )
VAR NextReviewDate = MINX ( NextReviewTable, [Date] )
VAR PrevReviewTable = FILTER ( ReviewTable, [Date] <= CurrentDate )
VAR PrevReviewDate = MAXX ( PrevReviewTable, [Date] )
VAR StartDate = IF ( RevCycleNum = 1, [LeadtimeStart], PrevWorkDate )
VAR EndDate = IF ( CurrentDate = MaxDate && [Status] <> "Work", PrevReviewDate, COALESCE ( NextReviewDate, TODAY ( ) ) )
VAR Days = NETWORKDAYS ( StartDate, EndDate, 1 )
RETURN
ROW ( "@RevCycleNum", RevCycleNum, "@Days", Days )
)
VAR T3 = SELECTCOLUMNS ( T2, "@@RevCycleNum", [@RevCycleNum], "@@Days", [@Days] )
RETURN
SUMX ( DISTINCT ( T3 ), [@@Days] )``````
Frequent Visitor

Thank you so much @tamerj1 ! I truly appreciate the work and time you have spend on helping me.

Frequent Visitor

Thank you so much @tamerj1 !

Super User

@CBXS ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Frequent Visitor

Of course.

For each project i wish to calculate the time between the dates in Review and Work like so:

Project 1 (first 3 rows):

Work: 20-08-2022 10:00

Review: 21-08-2022 10:00

Leadtime for project 1 = DIFF(Review, Work) = 1 day

Project 2:

Work1: 21-08-2022 08:00
Review1: 22-08-2022 11:00

Work2: 23-08-2022 08:00

Review2: 24-08-2022 11:00

Leadtime for project 2 = DIFF(Review1, Work1) + DIFF(Review2, Work2) = 2,25 day

Project 3:

Leadtime for project 3 = DIFF(Review1, Work1) + DIFF(NOW, Work2) = 2,08 day

I hope I explained my problem clearer with this.

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 - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors