Forum Discussion
Filter rows based on dates comparison
Hi,
I have a table containing the vehicles that I currently have in my stock. The vehicles theirselves have a STOCK_START_DATE (which is the date they entered my stock in) and a STOCK_END_DATE (which is the date they exited my stock from).
On my report I wanted to filter all the vehicles that have the stock end date less than the maximium stock start date, i.e.:
STOCK_END_DATE < MAX(STOCK_START_DATE)
The problem is that, in a measure, I cannot compare an aggregate column with a non-aggregate one...
I also don't know if, once the measure is created, it will only need to use this measure as a filter to filter the rows in the above-mentioned fashion.
Can you help me out to do this?
Thank you very much!
Create measure
Measure =
var max_start=calculate(max(table[STOCK_START_DATE]),all(table))
return
if(max(table[STOCK_END_DATE])<max_start,1,-1)
And then add this measure to your visual level filter and set it to 1.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
6 Replies
Create measure
Measure =
var max_start=calculate(max(table[STOCK_START_DATE]),all(table))
return
if(max(table[STOCK_END_DATE])<max_start,1,-1)
And then add this measure to your visual level filter and set it to 1.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.Hi Anonymous ,
thank you very much for your answer!
I've set the measure that you suggested but I can't add it as a filter: when I drag and drop the measure to the filters nothing happens, as if this measured could not been accepted as a filter....
First drag table visual and in table visual drag required column.
then create the measure which i have suggested.
Add it to visual level filter(not page level filter) ans click on "is" and set it to 1 and then click on apply filters.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
