Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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
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
Hi @Lightup
You can create a disconnected date table that will be used in your visual
Date =
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 )
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |