Filter on record that has an differend date

[jorismo]

Hi all,

I was wondering if there is an way to achieve to following:

- In my raw data I've got several customers, statusses and open_dates
- In the reports I want to select one of the customers and that the report automatically looks for the first date that is different than 'Closed' and take the firtst date of that month.

I've created some screenshots to explain.

The raw data:

This is what I want to receive, a report for customer A but 
The first date that hasn't status <> Closed = 30/04/2020 so the report should take 01/04/2020 as startdate for filtering.

Example for customer B:

First date with status <> Closed 15/09/2020. So the filter should take 01/09/2020 as startdate

Exampel for customer C:

First date <> Closes = 19/01/2020. Filtering needs to be done on open_date = 01/01/2020 (so the record from 03/01/2020) should also be visible in the report.

I hope someone can help me on this, it would help me a LOT!

thanks!

[v-jayw-msft · Accepted solution]

Hi @jorismo ,

Create a measure as below then add it to visual filter.

Measure = 
var _min = CALCULATE(MIN('Table'[open date]),FILTER(ALLEXCEPT('Table','Table'[customer]),'Table'[status]<>"closed"))
return
IF(SELECTEDVALUE('Table'[open date])>=_min,1,0)

Best Regards,

Jay

[MFelix]

Hi @jorismo ,

Do you want this calculation to be dinamic based on the selection on of other fields on top of the customer or is it only the customer?

You could create an auxiliar table with the customer and the first date that is not closed and then use it has your filtering.

[jorismo]

Hi, 

sorry about the delayed response.
It's only on the Customer that I need the first date that is different than 'Closed'.