Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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

ProjectStatusDateLeadtime(days)
1Work20-08-2022 10:001,00
1Review21-08-2022 10:001,00
1Done22-08-2022 09:001,00
2Work21-08-2022 08:002,25
2Review22-08-2022 11:002,25
2Work23-08-2022 08:002,25
2Review24-08-2022 11:002,25
2Done25-08-2022 11:002,25
3Work24-08-2022 08:002,08
3Review25-08-2022 08:002,08
3Work28-08-2022 09:002,08

 

Any help will be much appreciated.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

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 )

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

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

1.png

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
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.

ProjectStatusDateLeadtimeStartLeadtime(days)
1Work20-08-202219-08-20222
1Review22-08-202219-08-20222
1Done22-08-202219-08-20222
2Work21-08-202219-08-20224
2Review22-08-2022 19-08-20224
2Work23-08-202219-08-20224
2Review24-08-2022 19-08-20224
2Done25-08-202219-08-20224
3Work24-08-202223-08-20227
3Review25-08-202223-08-20227
3Work29-08-202223-08-20227

 

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

 

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

CBXS
Frequent Visitor

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

1.png

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
Frequent Visitor

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

CBXS
Frequent Visitor

Thank you so much @tamerj1 ! 

amitchandak
Super User
Super User

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

Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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