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 NumberPick up dateDrop off dateCar classPick up location
026180C31/07/2025 12:00 31/12/2025 8:00 ICARBNE
026181C31/07/2025 12:00 28/11/2025 12:00 ICAHBNE
030109C31/07/2025 15:14 31/12/2025 15:14 IFARBNE
033310C30/07/2025 7:59 30/11/2025 8:00 ECAHBNE
041121C13/08/2025 7:12 27/08/2025 7:12 EFBRBNE
045926C25/07/2025 10:00 31/12/2025 8:00 IFARBNE


I'm trying to calculate the number of "OnRent" for any calendar date based on the table above. What I've done is I've created a calculated measure as such:

OnRent =
VAR tmpOnRent = ADDCOLUMNS('DemandPlotterRA+RES',"Pick up date", 'DemandPlotterRA+RES'[Pick up date],"Drop off Date",'DemandPlotterRA+RES'[Drop off date])
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpOnRent,
            'Calendar'
        ),
        [Date] >= [Pick up date].[Date] &&
        [Date] <= [Drop off date].[Date]
    ),
    "ID",if(isblank('DemandPlotterRA+RES'[Confirmation Number]),'DemandPlotterRA+RES'[RA Number],'DemandPlotterRA+RES'[Confirmation Number]),
    "Car class",'DemandPlotterRA+RES'[Car class],
    "Pick up location",'DemandPlotterRA+RES'[PU Location],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],[Car class],[Pick up location],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)



This works fine, and I was then able to plot a chart showing me what the demand looks like for the future. However, what this does is it counts every rental as one irrespective of the time of pick up or drop off. 

What I would like to do is have the following (example from the first row in the table above):

026180C 31/07/2025 12:00 31/12/2025 8:00 ICAR BNE

I would like my OnRent calculation to show 0.5 on the 31/07/25, 1 for all dates until 30/12/25 and 0.33 for 31/12/25. Essentially the time difference in hours between each date generated and the dates above. 

Any help appreciated 🙂 


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 )

3 Replies

  • 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.

     

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    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 )

    • AlienBI's avatar
      AlienBI
      Frequent Visitor
      1 year ago

      Perfect, thanks so much! It works exactly as intended and it's elegant 🙂