Forum Discussion

Lightup's avatar
Lightup
Regular Visitor
4 years ago
Solved

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...
DAX
Desktop
Power BI Dax
powerbi
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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 )