Forum Discussion
Time Duration Calculation
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] ) + 1https://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.
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
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])
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.
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?
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 delimiterHi,
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