Forum Discussion
Hour Split Across Multiple Months
An employer may sign up for a project that may get finished a day or within a month. But other times the project may span multiple months. For example, a project started on 27/06/2021 and ended on 21/11/2021, I want to be able to see the duration spread across June to November (duration total for June, July, August etc) rather than aggregating the duration total to just June. The total should not be for June only. It should be spread across multiple months for the project duration.
I want to be able to display results like this:
Hello:
You can give this a go. I entered in my own figures, but appear to spread correctly. Link attached.https://drive.google.com/file/d/1Jez3eD5EY8FKBicRzwyDoK0jDXdzO6-2/view?usp=sharing
2 Replies
Whitewater100Solution Sage
Hello:
You can give this a go. I entered in my own figures, but appear to spread correctly. Link attached.https://drive.google.com/file/d/1Jez3eD5EY8FKBicRzwyDoK0jDXdzO6-2/view?usp=sharing
- tamerj1
Community Champion
Hi Lightup
You can create a disconnected date table that will be used in your visualDate = CALENDAR ( MIN ( 'Table'[Project_StartDateTime] ), MAX ( 'Table'[Project_EndDateTime] ) )The use the following measure to calculate the number of hours
Duration ( Hours) = VAR FirstDateSelected = MIN ( 'Date'[Date] ) VAR LaststDateSelected = MAX ( 'Date'[Date] ) VAR SatrtProjectDate = SELECTEDVALUE ( 'Table'[Project_StartDateTime] ) VAR EndProjectDate = SELECTEDVALUE ( 'Table'[Project_EndDateTime] ) VAR StartDateTime = IF ( FirstDateSelected <= SatrtProjectDate, FirstDateSelected, SatrtProjectDate ) VAR EndDateTime = IF ( LaststDateSelected >= EndProjectDate, LaststDateSelected, EndProjectDate ) RETURN DATEDIFF ( StartDateTime, EndDateTime, HOUR )
