Filter rows based on dates comparison

[AMS_Pag]

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!

[Anonymous · Accepted solution]

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.

[AMS_Pag]

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....

[Anonymous]

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.

[AMS_Pag]

Sorry but I didn't understand what you mean by:

---

@Anonymous wrote:

First drag table visual and in table visual drag required column.

---

Can you explain me with more details?

Thanks again!

[Anonymous]

There is visualization section.

Select table visual.

Then drag required fields in table visual.

and drag measure to visual level filter.

[AMS_Pag]

Thanks, it works!