topic Re: Calculating Rental $'s per day on a calendar table, using a start date column in DAX Commands and Tips

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

CBloome77 — Tue, 15 Oct 2024 18:01:35 GMT

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.

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

SachinNandanwar — Tue, 15 Oct 2024 19:56:10 GMT

Can you post some sample data ?

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

Anonymous — Wed, 16 Oct 2024 05:48:06 GMT

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.

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

CBloome77 — Wed, 16 Oct 2024 13:05:46 GMT

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!!!!