Construct calculated column for time spent on "Work"

[CBXS]

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.
Leadtime(days) is my desired column.

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.

[tamerj1 · Accepted solution]

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 = 
    ADDCOLUMNS ( 
        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 )

[CBXS]

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

[tamerj1]

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

[CBXS]

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

[tamerj1]

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] )

[CBXS]

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

[CBXS]

Thank you so much @tamerj1 ! 

[amitchandak]

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

Appreciate your Kudos.

[CBXS]

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.