Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lightup
Regular Visitor

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.


 

Screenshot 2022-03-27 180850.png

I want to be able to display results like this:

Lightup_0-1648403395460.png

 

 

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution 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 

 

Whitewater100_0-1648472595041.png

 

View solution in original post

2 REPLIES 2
Whitewater100
Solution Sage
Solution 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 

 

Whitewater100_0-1648472595041.png

 

tamerj1
Super User
Super User

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 )

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors