Time Duration Calculation

[ramhariessentia]

Hi all, I have a problem of finding the total time duration of various projects (Refer to snapshot below).

I want to calculate the total time for each project (ie difference of completion date between the last activity & First activity).

How to do that using measure or Power query ?

[Jihwan_Kim · Accepted solution]

Hi, @ramhariessentia 

Please correct me if I wrongly understood your question.

I tried to create a sample by myself based on the information in your screenshot.

The sample pbix file's link is down below.

Total Days Each Project =
VAR currentproject =
MAX ( 'Table'[ProjectID] )
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[ProjectID] = currentproject ),
'Table'[ProjectID],
"@duration", DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY )
)
RETURN
MAXX ( newtable, [@duration] ) + 1

https://www.dropbox.com/s/npv905vo4zi4gsk/ramhariessentia.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your explanation.

please kindly check the below measure and the link.

I amended the measure to omit    < abc-start ~ next of abc-start >

Total Days Each Project =
VAR currentproject =
MAX ( 'Table'[ProjectID] )
VAR abcactivitystartdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ProjectID] = currentproject
&& 'Table'[Activity] = "abc"
)
)
VAR abcactivityfinishdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ProjectID] = currentproject
&& 'Table'[Date] > abcactivitystartdate
)
)
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[ProjectID] = currentproject ),
'Table'[ProjectID],
"@duration", DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY )
)
RETURN
MAXX ( newtable, [@duration] ) + 1
- DATEDIFF ( abcactivitystartdate, abcactivityfinishdate, DAY )

https://www.dropbox.com/s/npv905vo4zi4gsk/ramhariessentia.pbix?dl=0 

[ramhariessentia]

Hi all Power BI gurus,

With reference to trailing topic, I need to calculate average time taken for all projects.

Please help

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[ramhariessentia]

Thanks Ashish,

Please find below snapshot f sample data with the help of Jihwan, I was able to find total duration of project , Now I want to find the average time taken for a project, I used averageX function, but the result is sightly more than I expected. I have created a sample Power BI file, Link attached  Sample file .

thanks in advance

[Ashish_Mathur · Accepted solution]

What answer are you expecting in the card visual?  Does this measure work?

Measure 1 = AVERAGEX(VALUES('Sample data'[Project ID]),[Sample Total Project Time])

[ramhariessentia]

I am expecting an answer 706 as that is the true mean.

[ramhariessentia]

Sorry Ashish, my mistake your solution worked

[Ashish_Mathur]

You are welcome.

[ramhariessentia]

Thanks heaps it worked, I need some further help as well what if i need to omit certain activity form calculations for example I need should not consider activity 'abc' as it's just initiation & it may or may no be part of the list of

activities in the project, please find a list of Activities My issue is there might be a significant time lag between design information & design checking  so i need to omit design information when calculating duration.

acitivities.

[Jihwan_Kim]

Hi, @ramhariessentia 

Thank you for your feedback.

Terribly sorry that I could not understand your question.

- Now, each Fee Name has a number. Is it a different sample?

- I could not find the Fee name, Design Checking.

- Is the number accumulate duration hours? Or, just a duration of hours?

[ramhariessentia]

Hi with reference to your example what if I want to omit activity abc when calculating total duration of project. 

with reference to my image for the previous post, those are the actual activity name the numbers are nothing but a split by delimiter

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your explanation.

please kindly check the below measure and the link.

I amended the measure to omit    < abc-start ~ next of abc-start >

Total Days Each Project =
VAR currentproject =
MAX ( 'Table'[ProjectID] )
VAR abcactivitystartdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ProjectID] = currentproject
&& 'Table'[Activity] = "abc"
)
)
VAR abcactivityfinishdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ProjectID] = currentproject
&& 'Table'[Date] > abcactivitystartdate
)
)
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[ProjectID] = currentproject ),
'Table'[ProjectID],
"@duration", DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY )
)
RETURN
MAXX ( newtable, [@duration] ) + 1
- DATEDIFF ( abcactivitystartdate, abcactivityfinishdate, DAY )

https://www.dropbox.com/s/npv905vo4zi4gsk/ramhariessentia.pbix?dl=0 

[ramhariessentia]

Hi Jihwan,

Is the same formula can be applied in a column

[ramhariessentia]

Hi further what if i want to calculate the average time taken for all projects can you send me a formula for that.

[Jihwan_Kim · Accepted solution]

Hi, @ramhariessentia 

Please correct me if I wrongly understood your question.

I tried to create a sample by myself based on the information in your screenshot.

The sample pbix file's link is down below.

Total Days Each Project =
VAR currentproject =
MAX ( 'Table'[ProjectID] )
VAR newtable =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[ProjectID] = currentproject ),
'Table'[ProjectID],
"@duration", DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY )
)
RETURN
MAXX ( newtable, [@duration] ) + 1

https://www.dropbox.com/s/npv905vo4zi4gsk/ramhariessentia.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.