cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMS_Pag
Frequent Visitor

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

View solution in original post

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
Not applicable

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.

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
Not applicable

There is visualization section.

 

Select table visual.

 

Then drag required fields in table visual.

and drag measure to visual level filter.

 

Thanks, it works!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors