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

DAX DAX Calculations
1

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 155.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,

2

Hi @Vsb79,

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

Rental WorkingDays = 
VAR vTable = 
    ADDCOLUMNS(
        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 = 
ADDCOLUMNS(
    ADDCOLUMNS(
        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

image

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

I hope this is helpful

4 Likes
3

Hi @vab79, welcome to the forum! :slight_smile:

Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

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
6

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?

RentalsPBI
RentalsPBI1498Ɨ747 25.9 KB

Thankyou

7

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

I hope this is helpful

Should you require further assistance, please create a new topic, with detailed requirement and provide your (mock up) work in progress file. Members on this forum will gladly assist you.

1 Like