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.
Thanks for the posting tip - that makes sense.
Since I'm not using the old data anymore and am rebuilding this, not all my terminology was exactly the same, so I changed your code to the current terminology, but I'm getting an error (and can't figure out why.
Current Rentals = VAR _maxDate = MAX( 'Calendar'[Date] ) 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" } )Here's an image of the error:
And here's an image of the applicable tables/fields:Thanks, d_gosbell for your help!
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" } )