Forum Discussion

AlienBI's avatar
AlienBI
Frequent Visitor
1 year ago
Solved

Calculating hours for each dates between 2 date columns

Hi there,  I've got a little problem which I hope you can help me with. Got a table like this one here (thousands of rows) - car rental business so you understand what this means: Confirmation...
Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello AlienBI 

     

    Try this DAX Measure

    OnRent =
    VAR tmpOnRent =
    ADDCOLUMNS (
    'DemandPlotterRA+RES',
    "Pickup", 'DemandPlotterRA+RES'[Pick up date],
    "Dropoff", 'DemandPlotterRA+RES'[Drop off date]
    )
    VAR tmpTable =
    ADDCOLUMNS (
    FILTER (
    GENERATE ( tmpOnRent, 'Calendar' ),
    [Date] >= INT ( [Pickup] )
    && [Date] <= INT ( [Dropoff] )
    ),
    "RentalHours",
    VAR StartOfDay = [Date] // midnight
    VAR EndOfDay = [Date] + 1 // next midnight
    VAR StartTime = MAX ( [Pickup], StartOfDay )
    VAR EndTime = MIN ( [Dropoff], EndOfDay )
    RETURN
    DIVIDE ( DATEDIFF ( StartTime, EndTime, MINUTE ), 60, 24 )
    )
    RETURN
    SUMX ( tmpTable, [RentalHours] / 24 )

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi AlienBI 

Use a disconnected calendar table and create this measure 

Count by Time Period -  Day and Time = 
VAR StartDate = MIN ( CalendarTable[Date] )
VAR EndDate   = MAX ( CalendarTable[Date] )

RETURN

SUMX (
    FILTER (
        Rentals,
        Rentals[Pick up date] <= EndDate
            && Rentals[Drop off date] >= StartDate
    ),
    VAR PickupDateTime =
        Rentals[Pick up date] + Rentals[Pickup time]        -- use datetime column if not separate
    VAR DropoffDateTime =
        Rentals[Drop off date] + Rentals[Drop off time]     -- use datetime column if not separate

    -- Window we’re checking (full days)
    VAR WindowStart = StartDate
    VAR WindowEnd   = EndDate + 1   -- midnight after EndDate

    -- Overlap window
    VAR OverlapStart = MAX ( PickupDateTime, WindowStart )  -- counts only a portion of the day if the actual date time is greater than the current date
    VAR OverlapEnd   = MIN ( DropoffDateTime, WindowEnd )   -- counts only a portion of the day if the actual end time is lesser than the next date after current at midnight
    VAR OverlapHours =
        MAX ( 0, DATEDIFF ( OverlapStart, OverlapEnd, HOUR ) ) -- count the difference in hours between adjusted start and end datetimes

    RETURN DIVIDE ( OverlapHours, 24 )
)

Please see the attached pbix.