Calculating Rental $'s per day on a calendar table, using a start date column

[CBloome77]

Greetings.  

I have a list of rental vehicles with the columns:  Asset#, Rental Start Date, and Rental $ per day. 

I have also created a Calendar table using the CALENDARAUTO function.

What I'm looking to do, is create a SUM(Rental $ per day) for each date on the calendar table, based on the rental start date, meaning, if the date in the Calendar table is 4/1/24, and car 1 and car 2 were rented prior to that date, I want the result to be the sum of the daily rental price for car 1 and car 2.

I thought to use Calculate, but it won't let me reference the two date columns as a filter.  I've tried several different ways, including using today() as an end date column, date range filters, it just doesn't work.  I know this is probably something very basic that I'm missing, but I just can's seem to find anything to reference.

[Anonymous · Accepted solution]

Hi @CBloome77 

Please make sure that no relationship has been established between your Calendar and RentalVehicles table.

I'm assuming TODAY() as the Rental End Date, if you have a Rental End Date column in your data, just use it instead of TODAY().

1. Create a Rental End Date column in your RentalVehicles table:

Rental End Date = TODAY()

2. Create a measure to calculate total rental  $ per day:

TotalRentalPerDay = 
CALCULATE(
    SUM('RentalVehicles'[Rental $ per day]),
    FILTER(
        'RentalVehicles',
        'RentalVehicles'[Rental Start Date] <= MAX('Calendar'[Date]) &&
        'RentalVehicles'[Rental End Date] >= MIN('Calendar'[Date])
    )
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CBloome77]

This is it!  Thanks so much!  I think I was getting hung up on using the CALENDARAUTO function.  Regardless, this worked great, and will help me on many reports I'm working on!  THanks!!!!

[SachinNandanwar]

Can you post some sample data ?