Seemingly simple date issue...
My company rents band & orchestra instruments to school children. I have a handful of data query tables and I've created a calendar table. For this issue, I think all I need to focus on are thes...
Forum Discussion
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.
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] ).
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.
