Forum Discussion
Seemingly simple date issue...
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.