Seemingly simple date issue...

Oops, sorry I missed out the RETURN statement in the expression. Try the following:

Current Rentals = 
VAR _maxDate = MAX( 'Calendar'[Date] )
RETURN CALCULATE(
	COUNTROWS('scrental'),
	FILTER( ALL('scrental'[Contract Date],'scrental'[Return Date]) 
		'scrental'[Contract Date] >= _maxDate
		&& ( 'scrental'[Return Date] = BLANK() 
                   || 'scrental'[Return Date] <= _maxDate )
	)
	,'scrental'[Dunn] IN {"P" ,"N" }
)

d_gosbell,

I hate to do this again, but still a syntax error...

yep, just a missing comma after the ALL() function. 

Current Rentals =
VAR _maxDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'scrental' ),
        FILTER (
            ALL ( 'scrental'[Contract Date], 'scrental'[Return Date] ),
            'scrental'[Contract Date] >= _maxDate
                && (
                    'scrental'[Return Date] = BLANK ()
                        || 'scrental'[Return Date] <= _maxDate
                )
        ),
        'scrental'[Dunn] IN { "P", "N" }
    )

d_gosbell,

Thanks again for all your help. Unfortunately, while the change got your code working, it isn't doing what I am expecting it to do:

"Total Current Rentals" should be 2280. The Current Rentals have all but disappeared from "New vs. Current Rentals" and the "% Renting, Returned & Owned" reports - this year's info is all that seems to remain, but not what I believe to be the exact number, but close.

On the report I've been trying to get ("Current Rentals Over Time"), I added the arrows, not to point to anything, but to show what I would roughly expect the graph to look like. We rent a lot of instruments around back-to-school time, then some slowly get returned over the course of the year, then we get a bunch more the next fall, etc.

Let me know if you have any other ideas. Thanks for your help.

Can you post an example data set (it only has to be enough rows to illustrate your scenario)?

If you posted 10-20 rows and were able to say that based off that data set you would expect the result for month 1 to be x, month 2 to be y and month 3 to be z. At the moment I'm speculating about what I think your data probably looks like and I'm obviously making an incorrect assumption somewhere.

d_gosbell,

As time passes, I simply want to see what the current rentals count is over time. Here's an image of something I worked up quickly.

Let me know if you have any questions. If it helps you think through things, these are long term rentals that people rent toward ownership, not instruments that are rented for a few days and brought right back.

d_gosbell,

Of course, no sooner than I post, then I see a slight mistake in my data. There is a single instrument that has been paid off (Primary Key #8), reducing the total number of current rentals by one. The problem is, I don't see anywhere in the data provided by our PoS software that gives a payoff date, so I'm not sure how or when that would get calculated. This could be a problem... ugh

Obviously I can't really help with the missing pay out date - you'll need to dig through your data and see if you can find if it's possible to link this up to a transaction or derive it in some other way. (for the time being I just excluded Dunn = "X" as they are not on going rentals)

So there was one basic issue in the calc in that I had inverted the greater than and less than conditions on the date filters. But the other thing I could see was that in the running calculation we don't actually want to exclude Dunn="R" otherwise we mask out all the return dates and we will not see any of those dips in the data like on Jan-6 where we have a return but no new rentals on that date.

So the following code gives me the same graph as the one you plotted in Excel

Current Rentals = 
VAR _maxDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'scrental' ),
        FILTER (
            ALL ( 'scrental'[Contract Date], 'scrental'[Return Date] ),
            'scrental'[Contract Date] <= _maxDate
                && (
                    'scrental'[Return Date] = BLANK ()
                        || 'scrental'[Return Date] > _maxDate
                )
        )
       , 'scrental'[Dunn] <> "X"
    )

The other thing I noticed was that the "P" records are payments. I assume being rentals that you would get multiple payments per instrument, so you may want to look at changing the COUNTROWS( 'scrental' ) to count distinct instrument, customer or contract id's with something like DISTINCTCOUNT( 'scrental'[contract id] ).

d_gosbell,

It's definitely closer to what I'm expecting to see. I'm sure it's still off, though, because of the payoff issue.

I think I found a date for rental payoffs. I imported another table, "sctrans", which is the Transactions table. Here's the information from that table I think you need:

Table Name: sctrans

Date Column: Gen Ledger Date

Column for types of Transactions: Transaction Type

Targeted Transaction Type: Contract Payoff

If you're able to add that into the code, it may do the trick. Thanks again for taking the time to help me figure this out - I greatly appreciate it!

I would not suggest solving this by changing the existing DAX to make it more complicated. I would suggest changing the query to look up the pay off date and insert it into the Return Date column (or make a new derived column if you like that combines the return date and payoff date). Then we just have to remove the filter excluding the X records and everything should just work.

d_gosbell,

That makes complete sense. Unfortunately, I'm not sure how to create the new column when the dates for returns are on one table ('scrental') and the dates of the payoffs are on another ('sctrans'). I've been trying to research it to no avail.