Multi days (date/time) rental daily revenue

DAX DAX Calculations
2

Hello @JoseMilhazes,

Thank You for posting your query onto the Forum.

To achieve the results based on the conditions that you’ve specified. Below are the measures alongwith the screenshot of the final results provided for the reference purposes -

Daily Rate = 
SUM( 'Hire Log - Data'[Daily rate] )



Hire Days = 
IF( ISBLANK( SELECTEDVALUE( 'Hire Log - Data'[Delivery Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( 'Hire Log - Data'[Pick Date] ) , 
            TODAY() ) , 
        ALLSELECTED( 'Hire Log - Data' ) ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( 'Hire Log - Data'[Pick Date] ) , 
            SELECTEDVALUE( 'Hire Log - Data'[Delivery Date] ) ) , 
        ALLSELECTED( 'Hire Log - Data' ) ) )



Total Revenue = 
[Daily Rate] * [Hire Days]

Final Results
Final Results830×357 20.3 KB

I’m also attaching the working of the PBIX file as well as providing a link below of the post where almost similar type of query was posted onto the forum and how the solution was achieved for your reference.

Also, if you would like to see the totals in your table and want to fix it. One of our expert had already created a post which addresses that issue in depth and contains the different types of scenario’s as well as link of the videos. Below is the link of that post provided for the reference purposes as well. In the current file as well, I’ve used the similar approach to fix the totals.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Notes:

1). In your data, for one of the row you didn’t had a “Delivery Date” so it’s presumed that it’s still in-transit as of today and rates continue to multiply with it .

2). For one of the rows, you had Pick Date as “29th January, 2021” and Delivery Date as “28th January, 2021” which is impossible unless your truck drivers are time travellers so I changed the Delivery Date to something else i.e. 28th February, 2021.

3). Your partial start date and end date are considered as one full day and accordingly the results are evaluated. You can modify the measure or formula as per your business scenario to suit your requirements, if necessary.

Thanks and Warm Regards,
Harsh

example - Harsh.pbix (140.0 KB)

2 Likes