Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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 )
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 )
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
No more than 5 cycles a project. Most will have 1-2.
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] )
Thank you so much @tamerj1 ! I truly appreciate the work and time you have spend on helping me.
@CBXS ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
24 | |
23 |