Forum Discussion
Seemingly simple date issue...
Hi kincaids,
Sorrr for inconvenience, I update the reply, please verify and feel free to ask if you have any question.
Best Regards,
Angelia
Well, things have changed - it took a couple years for me to get around to it, but I'm working on rebuilding my data, and this is still an issue. Again, I'm trying to get a line graph that shows how many current rentals we have at any given time over the past 6 years (and into the future). This is what it looks like right now:
Todays current rentals show up across all time, rather than the actual current rental count each moment in timecalendar DAX based on the same table the current rentals are taken fromcurrent parameters of the visualization in question
Your help in figuring out why it's showing today's current rental number across all time rather than what the current rental number actually was at each point in time would be greatly appreciated.
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!
