Calculating rental revenue each month

Apologies if I don’t get this explanation right and for the lack of detail.

I’m doing some modeling for a company that rents industrial equipment for periods of time that can span multiple months. Each month, they invoice the client an amount based on the per day rate of the rental items multiplied by the time between the last invoice date and the end of the month. The critical dates in the calculation are the “Last Date Invoiced” and the contract “Expiration Date” .

THe goal is to be able to forecast future revenue.

For example, if the Last Invoice Date is 30/06/2020 and the contract Expiration Date is 30/11/2020 the number of months in the period is 5. On 31/07/2020 they will be invoiced for an amount equal to the per day rate of the rental items times the number of days in the month. In this case 31.

I can use DATEDIFF to get the number of Days, Weeks, Months in the rental periods but if I try to use a date slicer, I get blank results. I’m sure the table relationships has something to do with it too but when I try different combinations, it still doesn’t work.

Many thanks in advance!

Hi @mkaess,

Will need something more to work with here… can you whip up some sample data, show how you intent to use the slicer and how you want to visualize the results.

Thanks!

I assumed as much. This is a sample of the table I’m working with. I have a date table as well (the one you shared). The idea is to have a between date slicer such that, regardless of the dates selected in the slicer, it will return the revenue amount expected within that time for any rental item after the first date in the slicer to the last date in the current month. Sorry if I’m being a bit vague but I’m only now getting my head around how their system works.

I’m happy with any visual at this point as long as I can get the amount so accounts can forecast revenue.

All right then, let’s see if this get’s the party started…

Based on your Data I created a Model with these Rentals and a Date table. Notice there is no relationship between them. You can always create that virtually using TREATAS.

Next I placed a Between filter on the Report Page and harvested the Date selection.

First Date = 
CALCULATE( MIN( Dates[Date] ), ALLSELECTED( Dates[Date] ))

and

Last Date = 
CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates[Date] ))

.

Next I created a Number of Days Measure, you don’t have to do this separately but I like measure branching and it allowed me to make a few variations to the same pattern quickly.

Num of Days =
VAR RangeStart = IF( [First Date] < [Last Invoiced Date], [Last Invoiced Date], [First Date] )
VAR RangeEnd = IF( [Last Date] > [Valid Date], [Valid Date], [Last Date] )
VAR NumDays = COUNTROWS( CALCULATETABLE( VALUES( Dates[Date] ), FILTER( ALL( Dates[Date] ), Dates[Date] > RangeStart && Dates[Date] <= RangeEnd )))
RETURN

NumDays

.
The Expected Amount then becomes something like

Expected Amount = [Num of Days] * SUM( Rentals[Unit Price pr Day] )
.

As I said created some variations the this as well, let me know how you get on with these.

Here is my sample file, I hope this is helpful.
eDNA - Calculating rental revenue each month.pbix (78.0 KB)

1 Like

Awesome! Thanks. I’ll be working on this again today and will update later. I did remember a way I’ve done this before but using SQL stored procedures to split the date range into individual days. It would work for this scenario but I’m trying not to do any of the modeling in SQL. I don’t like having to troubleshoot two data queries when things aren’t quite right.

1 Like

Looking very promising. Some verification of the unit price calculations is needed but that’s not related to this post. It’s complicated. I have noticed that when I copied the measures over to my model, the performance in loading the visual is VERY slow. Specifically when adding the price measures. Any thoughts?

You mentioned using TREATAS. Which measures would that apply to? The date ones or the value ones?

I’m only iterating over the dimension Date table, so not immediately…

There have been a couple of threads on performance, here’s a link to one but use the search option to find others. @Nick_M has shared some great content on that subject.

.

If you have multiple date fields in a single fact table BUT they need to be treated equally in your DAX calc you basically have 2 options; no relationship or all inactive relationships.

However your report could include other Measures for which filtering does need to take place on a single date field from that fact table then you can use TREATAS / USERELATIONSHIP depending on what choice you’ve made. Here’s a video explaining TREATAS

.
I hope this is helpful

1 Like

Thanks again for the help. The date logic works brilliantly but only if I select any field from the fact table which filters the results. If no filters are applied other than the date slicer, the totals are blank. For example, if I create a slicer for Contract No, the results are correct. However, if I remove that slicer filter, there are no totals.

Is your model set up like the sample file supplied in post #4?

If not please share a sample, you can mask your sensitive data using this technique

The model is similar but the data in what was posted is only a sample. I think that’s why your’s works and I’m having issues. There are a number of blank fields on my fact table. I’ll work on it a bit more and I I’m still stuck, I’ll upload a cleaned up pbix file. Great video by the way!

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!

Hello Melissa - I’m still working through the DAX solution provided but to save me some time I’ve modeled this as a view in SQL using a technique I’ve used before. I will work out you solution but the client is putting pressure on to get this report working. Here is the SQL I’ve used.

SELECT d.[Date],
rl.[Contract No_],
rl.[Line No_],
rl.No_ “Object No”,
rl.[Description],
rl.[Customer No_] “Customer No”,
rl.[Invoiced To Date],
rl.[Expiration Date],
rl.[On-Rent Date],
rl.[Shipment Date],
rl.[Unit Price pr Day],
rl.[Line Amount (Period)] “Line Amount”,
rl.[Line Discount Amount],
rl.[Rental Period],
rl.[Price Term Code],
re.[Return Date],
rl.[Shortcut Dimension 1 Code] “Brand”,
rl.[Shortcut Dimension 2 Code] “Branch”,
rl.[Entry Status],
rl.[Type]
FROM dbo.[SFI$EQM Rental Line] rl
INNER JOIN
(SELECT [Date] FROM dbo.Calendar) d
ON d.[Date] >= rl.[Invoiced To Date]
AND d.[Date] <= rl.[Expiration Date]
LEFT OUTER JOIN dbo.[SFI$EQM Rental Return Entry] re
ON rl.[Contract No_] = re.[Contract No_]
AND rl.No_ = re.No_
AND rl.[Line No_] = re.[Ext_ Rental Line No_];
GO