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!