Calculating rental payment due each month

[matthewkaess]

Apologies if I don't get this explanation right.

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!

[v-lili6-msft]

hi  @matthewkaess 

For your case, it is a start date and end date problem, you could try this way as below:

Step1:

you need a separate date table, do not create relationship with fact table

Step2:

Create a measure as below:

Total = 
var _table=ADDCOLUMNS('Date',"_unit price per day",CALCULATE(SUM('Table'[Unit price pr Day]),FILTER('Table',MIN('Date'[Date])>'Table'[Invoiced To Date]&&MAX('Date'[Date])<='Table'[Expiration Date])))
return
SUMX(_table,[_unit price per day]
)

Step3:

Drag date field from this separate date into slicer and other visual.

and here is a similar post, you could refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

here is my sample pbix file, please try it.

Regards,

Lin

[matthewkaess]

Thank you. I will work on this again after the weekend and update the post.

[amitchandak]

@matthewkaess , refer if these two can help

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

[harshnathani]

Hi @matthewkaess ,

Can you share some sample data pls.

Regards,

HN

[matthewkaess]

[matthewkaess]

Can do but is is possible to upload an Excel file?