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)