Multi days (date/time) rental daily revenue

[JoseMilhazes]

Hi Guys, and thank you for your support:

I have a truck fleet, and every truck is rented on the spot market (to pick a cargo from A to B) and based on a daily rate.

In the “Hire Log” table, I have the hiring pick date and time, the delivery date and time, and the Daily rate. To have the relationship with my “DIM_Date,” I duplicate both columns’ dates to have just the date!

From the “Hire Log” table, I create a new table, “Fact_Daily_revenue”, as per the example with the one entry per day for each hires day!

I need some help because the time is not taken into consideration on the daily cost revenue. If I take the Date/time column in the crossjoin, I do not build the table!
So in the example, you can see that the hire starts at different times of the day and normally also delivers at other times of the day, so in the first and last renting day, the partial day needs to multiply with hired rate.
Thank you for your support
Jose Milhazes
example.pbix (99.2 KB)

[Harsh]

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]

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.

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)

Calculating Duration Between Two Dates Not Including Weekends and Holidays

Hello @JDopson, Thank You for posting your query onto the Forum. Just few days back I encountered this very similar type of problem onto the Forum. The only difference being is, in your current query you also have the “Time” aspect whereas the previous one was only limited to the “Date” aspect. So now, since you’ve a comination of “Date and Time” within your columns in the table. The first thing is, to separate them i.e. have individual columns for date as well as for time so that time-intell…

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[JoseMilhazes]

Hi @Harsh, thank you so much for your reply!
Starting with the notes: 1st indeed if blank the truck still in transit at contracted rate; 2nd I’m sorry and thank you. Did fix with the same date.

The 3rd was the help I was requesting. Our customers do not what to pay the full day!
Example:

Pick Date & Time: 2/28/21 7:00 PM

So for that day will be >> 2/28/21 7:00 PM - 2/28/21 12:00 AM = 0.791666667 (day)

So on Fev/28 the revenue was >> 0.791666667 * $500 = $395.83

In the attachment, you can find the files with the fix Date!

I’m converting the “Hire Log” into a FACT_DAY_Renevue since there are other types of revenues and costs per day (e.g. Driver; Fuel; Positioning; Cleaning, Etc). From there we will trace the total profit per day!

Thank you so much for the effort, warm regards, and be safe!

Jose Milhazes
Example v1.xlsx (12.5 KB)
example.pbix (101.3 KB)

[Harsh]

Hello @JoseMilhazes,

So to achieve the results as per the third condition that you’ve specified wherein your customers don’t want to pay for an entire day but want to pay proportionally. Below is the new measure alongwith the screenshot of the final results provided for the reference -

Hire Days - 2 = 
IF( ISINSCOPE( 'Hire Log - Data'[Pick Date] ) , 
    IF( ISBLANK( SELECTEDVALUE( 'Hire Log - Data'[Delivery Date] ) ) , 
        FIXED( ( TODAY() + TIME( 23 , 59 , 59 ) ) -
               ( SELECTEDVALUE( 'Hire Log - Data'[Pick Date] ) + SELECTEDVALUE( 'Hire Log - Data'[Pick Time] ) ) , 
            4 , 
            0 ) ,
        FIXED( ( SELECTEDVALUE( 'Hire Log - Data'[Delivery Date] ) + SELECTEDVALUE( 'Hire Log - Data'[Delivery Time] ) ) -
               ( SELECTEDVALUE( 'Hire Log - Data'[Pick Date] ) + SELECTEDVALUE( 'Hire Log - Data'[Pick Time] ) ) , 
            4 , 
            0 ) ) , 
    BLANK() )

Logic for measures - “Daily Rate” and “Total Revenue” revenue remains the same.

I’m also attaching the working of the Excel File (wherein you can cross-verify my Hire Days calculation) and PBIX file for the reference purposes.

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

P.S.:- I did posted the solution about 9 hours ago but really don’t know why the post didn’t came through or didn’t got uploaded.

Thanks and Warm Regards,
Harsh

Hire Log - Data - Harsh.xlsx (9.7 KB)

example - Harsh v2.pbix (140.0 KB)

[JoseMilhazes]

Thank you @Harsh

[Harsh]

Hello @JoseMilhazes,

You’re Welcome.

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh