topic Re: Hour Split Across Multiple Months in DAX Commands and Tips

Hour Split Across Multiple Months

Lightup — Sun, 27 Mar 2022 17:50:18 GMT

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:

Re: Hour Split Across Multiple Months

tamerj1 — Mon, 28 Mar 2022 03:55:29 GMT

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 )

Re: Hour Split Across Multiple Months

Whitewater100 — Mon, 28 Mar 2022 13:03:30 GMT

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