Forum Discussion

CBloome77's avatar
CBloome77
Frequent Visitor
1 year ago
Solved

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

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's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      CBloome77
      Frequent Visitor
      1 year ago

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