Forum Discussion
Seemingly simple date issue...
The more I'm thinking about this, I'm sure a lot of the problem now revolves around table relationships - there currently isn't one between the calendar table and any other table.
I did try to make a 1:* relationship between the date column in the calendar and the system date column in the scrental table, but that screwed a lot of my data up. I deleted the relationship, then deleted and re-added the system date to the different visuals that used it as the Axis, and everything is now back to normal.
I would really appreciate some help on this - thanks!
If you alter the expression from your original calculated column to something like the following it should work as a measure. The code below assumes that you don't have an active relationship between the 'Date' table and the rentals table.
Current Rentals =
VAR _maxDate = MAX( 'Date'[Date] )
CALCULATE(
COUNTROWS('Rental Accounts - scrental'),
FILTER( ALL('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[Return Date])
'Rental Accounts - scrental'[Contract Date] >= _maxDate
&& ( 'Rental Accounts - scrental'[Return Date] = BLANK()
|| 'Rental Accounts - scrental'[Return Date] <= _maxDate )
)
,'Rental Accounts - scrental'[rnt_dunn] IN {"P" ,"N" }
)If you did have a relationship between your date table and your rentals table you could use the following variation of the measure that uses the CROSSFILTER function to effectively turn off the relationship.
Current Rentals =
VAR _maxDate = MAX( 'Date'[Date] )
CALCULATE(
COUNTROWS('Rental Accounts - scrental'),
FILTER( ALL('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[Return Date])
'Rental Accounts - scrental'[Contract Date] >= _maxDate
&& ( 'Rental Accounts - scrental'[Return Date] = BLANK()
|| 'Rental Accounts - scrental'[Return Date] <= _maxDate )
)
,'Rental Accounts - scrental'[rnt_dunn] IN {"P" ,"N" }
, CROSSFILTER( 'Rental Accounts - scrental'[Contract Date], 'Date'[Date], None)
)PS. Just an FYI, but in cases like this where you are taking a fresh look at an old problem it's probably better to start a new thread and link back to this old one rather than replying to the old thread. The reason is that people who answer questions on the forum would look at threads like this, see that there are 5 posts and assume you are in a back and forth discussion with someone about your issue.