topic Re: Construct calculated column for time spent on "Work" in DAX Commands and Tips

Construct calculated column for time spent on "Work"

CBXS — Mon, 29 Aug 2022 09:24:37 GMT

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.

Re: Construct calculated column for time spent on "Work"

amitchandak — Mon, 29 Aug 2022 10:50:14 GMT

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

Appreciate your Kudos.

Re: Construct calculated column for time spent on "Work"

CBXS — Mon, 29 Aug 2022 11:07:29 GMT

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.

Re: Construct calculated column for time spent on "Work"

tamerj1 — Mon, 29 Aug 2022 11:17:05 GMT

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 )

Re: Construct calculated column for time spent on "Work"

CBXS — Mon, 29 Aug 2022 11:22:50 GMT

Thank you so much tamerj1 !

Re: Construct calculated column for time spent on "Work"

CBXS — Thu, 01 Sep 2022 07:53:32 GMT

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

Re: Construct calculated column for time spent on "Work"

tamerj1 — Thu, 01 Sep 2022 09:44:58 GMT

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

Re: Construct calculated column for time spent on "Work"

CBXS — Thu, 01 Sep 2022 10:21:24 GMT

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

Re: Construct calculated column for time spent on "Work"

tamerj1 — Thu, 01 Sep 2022 17:15:47 GMT

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

Re: Construct calculated column for time spent on "Work"

CBXS — Fri, 02 Sep 2022 08:53:58 GMT

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