# Calculating the total hours driven within a selected date range when there is a start and end date

Hello all, I have a scenario, where I have vehicles that are rented out at different locations between some start dates and end dates.

|StartDate| EndDate Location ID| Drive Time/day| Car|
|Monday,Sept7,2020 |Sunday,Sept,27,2020| 121 |5.5| Toyota|
|Monday,Sept28,2020| Friday, Jan10,2021| 221| 5.5| Toyota|
|Saturday,Jan9,2021| Tuesday,Aug,17,20201| 321| 5.5| Toyota|

I have a date table(runs from 1/1/2021 to 12/31/2021) slicer that user selects, lets say the user selected a date range from 1/1/2021 to 1/30/2021, The total number of working days will be 21 days. I have to calculate the total number of drive time for the car within the date range in the date slicer selected for the different locations that have a start and end date.
So for location 121, since the start date and end date is not within the date slicer, that will not get calculated.
For location 221, Even though its start date is in sept 2020, the date slicer is from 1/1/2021, and the number of working days will be 6. The total number of hours driven should be 65.5
For the location 321, it will be calculated for 15 working days. The total hours driven should be 15
5.5
I was able to calculate the total number of working days. But trying to find out how to calculate the total hours driven wrt to date slicer selected that filters the start and end date.
Has anyone encountered this problem before? Appreciate your thoughts.

Thanks,

Hi @Vsb79,

Welcome to the Forum!
Give these two measures a go.

``````Rental WorkingDays =
VAR vTable =
SUMMARIZE( Rentals, Rentals[Location ID], Rentals[Car], Rentals[StartDate], Rentals[EndDate], Rentals[Drive Time/day] ),
"WorkingDays",
CALCULATE(
COUNTROWS ( 'Dates' ),
FILTER( Dates,
'Dates'[Date] >=  Rentals[StartDate] &&
'Dates'[Date] <=  Rentals[EndDate] -1 &&
'Dates'[IsWorkingDay] = TRUE
)
)
)
RETURN

SUMX( vTable, [WorkingDays] )
``````

and

``````Rental Drive time =
VAR vTable =
SUMMARIZE( Rentals, Rentals[Location ID], Rentals[Car], Rentals[StartDate], Rentals[EndDate], Rentals[Drive Time/day] ),
"WorkingDays", [Rental WorkingDays]
),
"Total Time", [Drive Time/day] * [WorkingDays]
)
RETURN

SUMX( vTable, [Total Time] )
``````

.
That gives me these results

Hereās the sample file.
eDNA - Drive time.pbix (50.5 KB)

4 Likes

Hi @vab79, welcome to the forum!

Hi Melissa, thank you for the warm welcome and thank you for the quick reply. This is exactly what I wanted. I did solve my scenario with the answer provided. Thank you for all the support.

1 Like

Melissa, I had a quick question regarding the data model and the relationships you created between date table and Rentals table. Why would there be an inactive relationship between these two tables? Also, at the same time, if I want to create a table visual with just the date table and Location ID, there should be a relationship between these two tables, right? And if I build a relationship(make the relationship active) between Date from dates table and StartDate from Rentals table, it does show that matrix but the other visual which shows drive time becomes blank. How can this be solved? If I were to have two different visuals?

Thankyou

This is due to your requirement. Active relationships between 2 tables make filtering happen AND there can be only one active relationship between 2 tables. For example with an active relationship on the Start date, location 221 wouldnāt be āvisibleā BUT with an active relationship on the end date 321 wouldnāt be visible because it falls outside of the selected date rangeā¦

.

Inactive Relationships can be āturn onā in measures with USERELATIONSHIP but they can also be created virtually with TREATAS for example